Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 265
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 265
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 265
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default 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


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Unique Values in a entire column, not just range Brian Excel Worksheet Functions 10 May 16th 09 08:48 PM
count unique in one column based on two other columns casey Excel Worksheet Functions 4 November 22nd 08 12:21 AM
How do I count unique values within a date range? Sam Excel Discussion (Misc queries) 5 June 22nd 07 06:15 AM
Count entries in one column based on values in another column Kurt Excel Worksheet Functions 7 January 10th 07 10:29 PM
Count cells based on date range in another column [email protected] New Users to Excel 1 May 5th 05 08:11 PM


All times are GMT +1. The time now is 12:11 PM.

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

About Us

"It's about Microsoft Excel"