Unique sumproduct with criteria!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a unique count using a sumproduct formula like this...

=sumproduct((D2:D100<>")/(countif(D2:D100,D2:D100)+(D2:D100="")

which seems to work ok. The problem is I then want to add another criteria
saying only count uniques where the date in column F = "01/01/05". Nothing
I've tried seems to work.

Thanks in advance!
 
Hi Naomi,
When you reference dates in formulas like this, the syntax is:

F2:F100=DATEVALUE("5/5/2003")

HTH
 
D F

Client 1 Jan-04
Client 2 Feb-04
Client 1 Jan-04
Client 2 Jan-04
Client 4 Jan-04

I would hope for this to give me a value of 3 but instead I get a value of
2.5?!
ie. Unique clients that also have Jan-04 in col F.
Thanks.
 
Thanks Ken,
I've changed it to...

=sumproduct((F2:F100=DATEVALUE("1/1/2004")/(countif(D2:D100,D2:D100)+(D2:D100=""))

but I am getting a result that is a few short of what it should be. Any
ideas? I'm going crazy here!!

Thanks.
 
It's hard for me to tell what's going on without seeing sample data. Perhaps
you could list a sample of 9-10 rows from columns D and F and tell us your
expected results?
 
Try the following...

=SUM(IF(FREQUENCY(IF((A1:A5<>"")*(B1:B5="1/1/2004"+0),MATCH(A1:A5,A1:A5,0
)),ROW(INDIRECT("1:"&ROWS(A1:A5))))>0,1,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
Back
Top