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)

 

[Guest]

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.

 

[Guest]

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!

 

[SandraRubart]

Choosing house colors using painting software

Hi

I'm about to paint my entire house and now choosing appropriate colors.
Found program to pick painting colors right on the photos of interior
or house facade, here's link : http://www.housepaintcolorschemes.com

Think it will help you too.

And by the way, if you wish to exchange photos in the format of this program,
please write me at: greensandra DOG gmx DOT com

Cheers,
Sandra