Counting Unique Values by Date Range

[jamison.folland]

Hello all,

First of all, new here but have been following all of your wonderful
advice for some time. So thanks for that. But I've registered
because I have an issue I can't seem to solve from current threads.

I need to find a count of unique values based on a date range. Here
is some sample data:

Column A----->Column B
Orange---------->01/01/2007
Orange---------->05/01/2007
Blue---------->08/01/2007
Blue---------->01/01/2006
Orange---------->07/01/2007
Red---------->06/01/2007
Red---------->06/01/2006
Yellow---------->07/01/2007
Green----------->08/01/2007

In this example, I would need to count the number of unique colours
(in this case, 5). Now, I'm currently doing that by using the
following formula:

=SUMPRODUCT(($A$2:$A$10<>"")/COUNTIF($A$2:$A$10,$A$2:$A$10&""))

I got this formula from this group, and it works wonderfully!

However, I now need to add a date criteria. For example, how do I
count the number of unique colours added no later than 6/01/2007 (in
this case, 3)?

I thank you in advance!

Cheers,
Jamison

 

[T. Valko]

Try this array formula** :

Color = range containing colors in column A
Date = range containing dates in column B

=COUNT(1/FREQUENCY(IF(Color<>"",IF(Date<>"",IF(Date<=DATE(2007,6,1),MATCH(Color,Color,0)))),ROW(Color)-MIN(ROW(Color)+1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Teethless mama]

Try this:

=SUM(IF(FREQUENCY(IF(Date<=--"2007-06-01",MATCH(Color,Color,0)),MATCH(Color,Color,0))>0,1))

ctrl+shift+enter, not just enter

 

[T. Valko]

=SUM(IF(FREQUENCY(IF(Date<=--"2007-06-01",MATCH(Color,Color,0)),MATCH(Color,Color,0))>0,1))

Returns an incorrect result if there are empty cells in the Date range.
Returns #N/A if there are empty cells in the Color range.

 

[Teethless mama]

OK... then try this:

=SUM(IF(FREQUENCY(IF((Date<=--"2007-06-01")*(Date<>""),MATCH(Color&"",Color&"",0)),MATCH(Color&"",Color&"",0))>0,1))

ctrl+shift+enter, not just enter

 

[jamison.folland]

Perfect, thanks much!