|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
May 16th, 2009, 04:44 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values in 1 Column based on Date Range in another Column
Howdy All,
I'm use the CountU function to count unique values in a column and it works great! Now what I want to do is count the unique values in Column B which occur between a specific date range in Column A. Column A contains dates from 2005 through 2009. Column B contains Order Numbers for each date. I want to count the unique values in column B for only 2008. Any Ideas? Thanks, Brian 
|
|
#2
May 16th, 2009, 05:08 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values in 1 Column based on Date Range in another Column
Assuming that the dates are true date values (day, month, and year), try
the following, which need to be confirmed with CONTROL+SHIFT+ENTER... =SUM(IF(FREQUENCY(IF(YEAR(A2:A100)=2008,IF(B2:B100 "",MATCH("~"&B2:B100, B2:B100&""))),ROW(B2:B100)-ROW(B2)+1),1)) or =SUM(IF(FREQUENCY(IF(A2:A100=DATE(2008,1,1),IF(A2 :A100=DATE(2008,12,31) ,IF(B2:B100"",MATCH("~"&B2:B100,B2:B100&"",0)))) ,ROW(B2:B100)-ROW(B2)+1 ),1)) -- Domenic http://www.xl-central.com In article , "Brian" wrote: Howdy All, I'm use the CountU function to count unique values in a column and it works great! Now what I want to do is count the unique values in Column B which occur between a specific date range in Column A. Column A contains dates from 2005 through 2009. Column B contains Order Numbers for each date. I want to count the unique values in column B for only 2008. Any Ideas? Thanks, Brian
|
|
#3
May 16th, 2009, 05:30 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values in 1 Column based on Date Range in another Column
There's more to this than what the OP stated!
In another post they wanted to count uniques for the *entire* column. http://groups.google.com/group/micro...e0a4037?hl=en# -- Biff Microsoft Excel MVP "Domenic" wrote in message ... Assuming that the dates are true date values (day, month, and year), try the following, which need to be confirmed with CONTROL+SHIFT+ENTER... =SUM(IF(FREQUENCY(IF(YEAR(A2:A100)=2008,IF(B2:B100 "",MATCH("~"&B2:B100, B2:B100&""))),ROW(B2:B100)-ROW(B2)+1),1)) or =SUM(IF(FREQUENCY(IF(A2:A100=DATE(2008,1,1),IF(A2 :A100=DATE(2008,12,31) ,IF(B2:B100"",MATCH("~"&B2:B100,B2:B100&"",0)))) ,ROW(B2:B100)-ROW(B2)+1 ),1)) -- Domenic http://www.xl-central.com In article , "Brian" wrote: Howdy All, I'm use the CountU function to count unique values in a column and it works great! Now what I want to do is count the unique values in Column B which occur between a specific date range in Column A. Column A contains dates from 2005 through 2009. Column B contains Order Numbers for each date. I want to count the unique values in column B for only 2008. Any Ideas? Thanks, Brian
|
|
#4
May 16th, 2009, 05:44 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values in 1 Column based on Date Range in another Column
Thanks Biff, appreciate it!
In article , "T. Valko" wrote: There's more to this than what the OP stated! In another post they wanted to count uniques for the *entire* column. http://groups.google.com/group/micro...functions/brow se_thread/thread/0172847a3e0a4037?hl=en# -- Biff Microsoft Excel MVP "Domenic" wrote in message ... Assuming that the dates are true date values (day, month, and year), try the following, which need to be confirmed with CONTROL+SHIFT+ENTER... =SUM(IF(FREQUENCY(IF(YEAR(A2:A100)=2008,IF(B2:B100 "",MATCH("~"&B2:B100, B2:B100&""))),ROW(B2:B100)-ROW(B2)+1),1)) or =SUM(IF(FREQUENCY(IF(A2:A100=DATE(2008,1,1),IF(A2 :A100=DATE(2008,12,31) ,IF(B2:B100"",MATCH("~"&B2:B100,B2:B100&"",0)))) ,ROW(B2:B100)-ROW(B2)+1 ),1)) -- Domenic http://www.xl-central.com In article , "Brian" wrote: Howdy All, I'm use the CountU function to count unique values in a column and it works great! Now what I want to do is count the unique values in Column B which occur between a specific date range in Column A. Column A contains dates from 2005 through 2009. Column B contains Order Numbers for each date. I want to count the unique values in column B for only 2008. Any Ideas? Thanks, Brian -- Domenic http://www.xl-central.com
|
|
#5
May 16th, 2009, 06:16 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values in 1 Column based on Date Range in another Column
Thanks to All.
I'm not trying to create confusion or hardship for anyone with multiple posts. I just wanted to know if I could use the COUNTU function to find uniques in a column which fall between dates in another column. THanks, Brian "T. Valko" wrote in message ... There's more to this than what the OP stated! In another post they wanted to count uniques for the *entire* column. http://groups.google.com/group/micro...e0a4037?hl=en# -- Biff Microsoft Excel MVP "Domenic" wrote in message ... Assuming that the dates are true date values (day, month, and year), try the following, which need to be confirmed with CONTROL+SHIFT+ENTER... =SUM(IF(FREQUENCY(IF(YEAR(A2:A100)=2008,IF(B2:B100 "",MATCH("~"&B2:B100, B2:B100&""))),ROW(B2:B100)-ROW(B2)+1),1)) or =SUM(IF(FREQUENCY(IF(A2:A100=DATE(2008,1,1),IF(A2 :A100=DATE(2008,12,31) ,IF(B2:B100"",MATCH("~"&B2:B100,B2:B100&"",0)))) ,ROW(B2:B100)-ROW(B2)+1 ),1)) -- Domenic http://www.xl-central.com In article , "Brian" wrote: Howdy All, I'm use the CountU function to count unique values in a column and it works great! Now what I want to do is count the unique values in Column B which occur between a specific date range in Column A. Column A contains dates from 2005 through 2009. Column B contains Order Numbers for each date. I want to count the unique values in column B for only 2008. Any Ideas? Thanks, Brian
|
|
#6
May 16th, 2009, 06:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values in 1 Column based on Date Range in another Column
It doesn't appear to take conditional arguments.
See your original post. -- Biff Microsoft Excel MVP "Brian" wrote in message ... Thanks to All. I'm not trying to create confusion or hardship for anyone with multiple posts. I just wanted to know if I could use the COUNTU function to find uniques in a column which fall between dates in another column. THanks, Brian "T. Valko" wrote in message ... There's more to this than what the OP stated! In another post they wanted to count uniques for the *entire* column. http://groups.google.com/group/micro...e0a4037?hl=en# -- Biff Microsoft Excel MVP "Domenic" wrote in message ... Assuming that the dates are true date values (day, month, and year), try the following, which need to be confirmed with CONTROL+SHIFT+ENTER... =SUM(IF(FREQUENCY(IF(YEAR(A2:A100)=2008,IF(B2:B100 "",MATCH("~"&B2:B100, B2:B100&""))),ROW(B2:B100)-ROW(B2)+1),1)) or =SUM(IF(FREQUENCY(IF(A2:A100=DATE(2008,1,1),IF(A2 :A100=DATE(2008,12,31) ,IF(B2:B100"",MATCH("~"&B2:B100,B2:B100&"",0)))) ,ROW(B2:B100)-ROW(B2)+1 ),1)) -- Domenic http://www.xl-central.com In article , "Brian" wrote: Howdy All, I'm use the CountU function to count unique values in a column and it works great! Now what I want to do is count the unique values in Column B which occur between a specific date range in Column A. Column A contains dates from 2005 through 2009. Column B contains Order Numbers for each date. I want to count the unique values in column B for only 2008. Any Ideas? Thanks, Brian
|
|
#7
May 16th, 2009, 08:08 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values in 1 Column based on Date Range in another Column
Could I use 2 helper columns like this:
Helper Column 1 - An IF statement that would display a 1 in the corresponding cells when the date in the DATE Column falls in year 2008. Helper Column 2 - An IF statement to pull the corresponding cell value from the ORDER NUMBER column IF Helper Column 1 equals 1. Then I could run COUNTU on Helper Column 2. If this is a viable scenario, what IF statement would I put in Helper Column 1? THanks, Brian "Brian" wrote in message ... Howdy All, I'm use the CountU function to count unique values in a column and it works great! Now what I want to do is count the unique values in Column B which occur between a specific date range in Column A. Column A contains dates from 2005 through 2009. Column B contains Order Numbers for each date. I want to count the unique values in column B for only 2008. Any Ideas? Thanks, Brian
|
|
#8
May 16th, 2009, 08:28 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values in 1 Column based on Date Range in another Column
How about...
C2, copied down: =IF(A2"",IF(YEAR(A2)=2008,B2,""),"") Then... =COUNTU(C2:C65536) -- Domenic http://www.xl-central.com In article , "Brian" wrote: Could I use 2 helper columns like this: Helper Column 1 - An IF statement that would display a 1 in the corresponding cells when the date in the DATE Column falls in year 2008. Helper Column 2 - An IF statement to pull the corresponding cell value from the ORDER NUMBER column IF Helper Column 1 equals 1. Then I could run COUNTU on Helper Column 2. If this is a viable scenario, what IF statement would I put in Helper Column 1? THanks, Brian "Brian" wrote in message ... Howdy All, I'm use the CountU function to count unique values in a column and it works great! Now what I want to do is count the unique values in Column B which occur between a specific date range in Column A. Column A contains dates from 2005 through 2009. Column B contains Order Numbers for each date. I want to count the unique values in column B for only 2008. Any Ideas? Thanks, Brian
|
|
#9
May 16th, 2009, 08:36 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values in 1 Column based on Date Range in another Column
Excellent Domenic!
Much simpler than my thought. Brian "Domenic" wrote in message ... How about... C2, copied down: =IF(A2"",IF(YEAR(A2)=2008,B2,""),"") Then... =COUNTU(C2:C65536) -- Domenic http://www.xl-central.com In article , "Brian" wrote: Could I use 2 helper columns like this: Helper Column 1 - An IF statement that would display a 1 in the corresponding cells when the date in the DATE Column falls in year 2008. Helper Column 2 - An IF statement to pull the corresponding cell value from the ORDER NUMBER column IF Helper Column 1 equals 1. Then I could run COUNTU on Helper Column 2. If this is a viable scenario, what IF statement would I put in Helper Column 1? THanks, Brian "Brian" wrote in message ... Howdy All, I'm use the CountU function to count unique values in a column and it works great! Now what I want to do is count the unique values in Column B which occur between a specific date range in Column A. Column A contains dates from 2005 through 2009. Column B contains Order Numbers for each date. I want to count the unique values in column B for only 2008. Any Ideas? Thanks, Brian
|
|
#10
May 16th, 2009, 09:32 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Unique Values in 1 Column based on Date Range in anotherColumn
Hello Brian,
Just for the fun of it: Array-enter =SUM(--(INDEX(pfreq(YEAR(A1:A76),B1:B76),,1)=2008)) But what's maybe more helpful: Select an area of two columns and as many rows as you have years and array-enter: =pfreq(pfreq(YEAR(A1:A76),B1:B76)) Not you have your statistic for all years. Pfreq you can get at my site www.sulprobil.com. Regards, Bernd
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
