Counting Unique Values by Date Range
 

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

Counting Unique Values by Date Range
 

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(2 007,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)

--
Biff
Microsoft Excel MVP


wrote in message
s.com...
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

Counting Unique Values by Date Range
 

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


" wrote:

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

Counting Unique Values by Date Range
 

=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.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
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


" wrote:

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

Counting Unique Values by Date Range
 

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


"T. Valko" wrote:

=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.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
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


" wrote:

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

Counting Unique Values by Date Range
 

Perfect, thanks much!