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

I am trying to count the number of unique cells in a range, where the
unique cells have a corresponding date that must fall within a
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-05
and 30-Sep-05 inclusive, I get 5. But how do I do this using excel
worksheet formulas?? I have tried the following array formula (enter
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 of
cells that are the first unique occurences. That is, just the unique
filter on ColA returns 12, BUT the first occurence of Jane Sheppard is
returned. This cell (A10 in this example) has a value 4-Aug-04 in B10
which does not satisfy the date conditions, hence the final sum is 4
and not 5.

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


--
Flystar
------------------------------------------------------------------------
Flystar's Profile: http://www.excelforum.com/member.php...fo&userid=9275
View this thread: http://www.excelforum.com/showthread...hreadid=470433

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 wrote:
I have spent 2 days now trying to get this to work, so any help would be
much appreciated. :)

I am trying to count the number of unique cells in a range, where the
unique cells have a corresponding date that must fall within a
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-05
and 30-Sep-05 inclusive, I get 5. But how do I do this using excel
worksheet formulas?? I have tried the following array formula (enter
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 of
cells that are the first unique occurences. That is, just the unique
filter on ColA returns 12, BUT the first occurence of Jane Sheppard is
returned. This cell (A10 in this example) has a value 4-Aug-04 in B10
which does not satisfy the date conditions, hence the final sum is 4
and not 5.

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

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

Aladin Akyurek wrote:

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))


[...]

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))


--
Flystar
------------------------------------------------------------------------
Flystar's Profile: http://www.excelforum.com/member.php...fo&userid=9275
View this thread: http://www.excelforum.com/showthread...hreadid=470433