| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Unique Values by Date Range
Perfect, thanks much!
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unique values from date range | Excel Discussion (Misc queries) | |||
| How do I count unique values within a date range? | Excel Discussion (Misc queries) | |||
| Counting Unique Values | Excel Discussion (Misc queries) | |||
| Counting unique records based on date range | Excel Worksheet Functions | |||
| Counting values within a Date Range | Excel Discussion (Misc queries) | |||
Linear Mode
