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

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

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

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

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

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

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

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

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

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

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

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

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

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

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