|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Count Unique Values in 1 Column based on Date Range in another Column
Bernd P,
I load the VBA for pfreq. The first formula gives me 0 and the second give me 2006. I'm a little lost about what to expect from that... Brian "Bernd P" wrote in message ... 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 |
Count Unique Values in 1 Column based on Date Range in another Column
Domenic,
This worked great. How could I modify the formula to include a rolling 12 month period? For clarification, I mean a the previous 12 months from today's date. 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 |
Count Unique Values in 1 Column based on Date Range in anotherColumn
Hello Brian,
Did you array-enter the formulas (finish with CTRL + SHIFT + ENTER, not only ENTER)? Please note that "Not you have your statistic for all years." should read "NOW you have your statistic for all years.". Regards, Bernd |
Count Unique Values in 1 Column based on Date Range in another Column
Is this what you mean?
E2: =EDATE(TODAY(),-12) F2: =TODAY() C2, copied down: =IF(A2<"",IF(A2=$E$2,IF(A2<$F$2,B2,""),""),"") Note that EDATE requires the Analysis ToolkPak to be enabled... Tools Add-Ins Analysis ToolPak -- Domenic http://www.xl-central.com In article , "Brian" wrote: Domenic, This worked great. How could I modify the formula to include a rolling 12 month period? For clarification, I mean a the previous 12 months from today's date. 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 |
Count Unique Values in 1 Column based on Date Range in another Column
Bernd,
Yes I got it to work, very interesting. The issue for me is that I the number of rows containing the data is large (around 20,000) and the number of rows varies. It does take some time to calculate on that many rows. Brian "Bernd P" wrote in message ... Hello Brian, Did you array-enter the formulas (finish with CTRL + SHIFT + ENTER, not only ENTER)? Please note that "Not you have your statistic for all years." should read "NOW you have your statistic for all years.". Regards, Bernd |
| All times are GMT +1. The time now is 08:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com