A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Count Unique Values in 1 Column based on Date Range in another Column



 
 
Thread Tools Display Modes
  #11  
Old May 16th, 2009, 09:52 PM posted to microsoft.public.excel.worksheet.functions
Brian[_16_]
external usenet poster
 
Posts: 34
Default 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  
Old May 17th, 2009, 05:58 AM posted to microsoft.public.excel.worksheet.functions
Brian[_16_]
external usenet poster
 
Posts: 34
Default 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  
Old May 17th, 2009, 10:20 AM posted to microsoft.public.excel.worksheet.functions
Bernd P
external usenet poster
 
Posts: 613
Default 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  
Old May 17th, 2009, 02:31 PM posted to microsoft.public.excel.worksheet.functions
Domenic[_2_]
external usenet poster
 
Posts: 265
Default 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  
Old May 17th, 2009, 02:58 PM posted to microsoft.public.excel.worksheet.functions
Brian[_16_]
external usenet poster
 
Posts: 34
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.