Advanced unique cell count with multiple conditions ... help!

[Flystar]

I have spent 2 days now trying to get this to work, so any help would b
much appreciated.

I am trying to count the number of unique cells in a range, where th
unique cells have a corresponding date that must fall within
particular period.
eg, consider the following data: (csv format)
#ColA,#ColB
Jane Smith,17-Dec-03
Luke Simons,21-Jun-04
Nick James,16-Dec-03
Bob Sampson,3-Jul-03
Greg Thingh,28-Nov-03
Kieran Smyth,23-Sep-04
Michael Smith,27-Aug-04
Michael Smith,30-Jul-04
Jane Sheppard,4-Aug-04
Borris Lee,19-Jul-05
Simon Johns,6-Sep-05
Alex Foote,12-Sep-05
Chris Hander,21-Sep-05
Jane Sheppard,24-Sep-05
Georgia Se,24-Sep-05

If I count all unique ColA cells that have a ColB date between 1-Sep-0
and 30-Sep-05 inclusive, I get 5. But how do I do this using exce
worksheet formulas?? I have tried the following array formula (ente
with CTRL+SHIFT+ENTER):
=SUM(IF(FREQUENCY(A2:A16,A2:A16)>0,IF(B2:B16>=DATE(2005,1,1),IF(B2:B16<=DATE(2005,9,30),1))))
The problem is, the above formula equates to 4.
I think what is happening is the frequency filter returns a range o
cells that are the first unique occurences. That is, just the uniqu
filter on ColA returns 12, BUT the first occurence of Jane Sheppard i
returned. This cell (A10 in this example) has a value 4-Aug-04 in B1
which does not satisfy the date conditions, hence the final sum is
and not 5.

Please please, if someone could help me to write a worksheet that wil
count unique cells with conditions such as in my example, I would b
ever so thankful!!! (no vba scripts please, worksheet formulas only

 

[Aladin Akyurek]

Let A2:B16 house the sample you provided.

Some options...

If you have Longre's morefunc.xll add-in...

=COUNTDIFF(IF($B$2:$B$16-DAY($B$2:$B$16)+1=D2,$A$2:$A$16,0),FALSE,0)

which must be confirmed with control+shift+enter.

Otherwise...

Either:

=SUMPRODUCT(--($A$2:$A$16<>""),--($B$2:$B$16-DAY($B$2:$B$16)+1=D2),--(MATCH($A$2:$A$16&$B$2:$B$16,$A$2:$A$16&$B$2:$B$16,0)=ROW($B$2:$B$16)-ROW($B$2)+1))

Or:

=SUM(IF(FREQUENCY(IF(($B$2:$B$16-DAY($B$2:$B$16)+1=D2)*($A$2:$A$16<>""),MATCH($A$2:$A$16,$A$2:$A$16,0)),ROW($B$2:$B$16)-ROW($B$2)+1)>0,1))

 

[Aladin Akyurek]

BTW, D2 houses a first day date like 1-Sep-05.

Let A2:B16 house the sample you provided.

Some options...

If you have Longre's morefunc.xll add-in...

=COUNTDIFF(IF($B$2:$B$16-DAY($B$2:$B$16)+1=D2,$A$2:$A$16,0),FALSE,0)

which must be confirmed with control+shift+enter.

Otherwise...

Either:

=SUMPRODUCT(--($A$2:$A$16<>""),--($B$2:$B$16-DAY($B$2:$B$16)+1=D2),--(MATCH($A$2:$A$16&$B$2:$B$16,$A$2:$A$16&$B$2:$B$16,0)=ROW($B$2:$B$16)-ROW($B$2)+1))


Or:

=SUM(IF(FREQUENCY(IF(($B$2:$B$16-DAY($B$2:$B$16)+1=D2)*($A$2:$A$16<>""),MATCH($A$2:$A$16,$A$2:$A$16,0)),ROW($B$2:$B$16)-ROW($B$2)+1)>0,1))

[...]

 

[Flystar]

Thanks Aladin, your options work wonderfully. For the benefit of others
the final formula I chose to use was:
=SUM(IF(FREQUENCY(IF(($B$2:$B$16-DAY($B$2:$B$16)+1=D2)*($A$2:$A$16<>""),MATCH($A$2:$A$16,$A$2:$A$16,0)),ROW($B$2:$B$16))>0,1)