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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 |
#15
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|