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

[Brian]

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]

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))

 

[T. Valko]

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/micr.../browse_thread/thread/0172847a3e0a4037?hl=en#

 

[Domenic]

Thanks Biff, appreciate it!

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/microsoft.public.excel.worksheet.functions/brow
se_thread/thread/0172847a3e0a4037?hl=en#

 

[Brian]

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]

It doesn't appear to take conditional arguments.

See your original post.

 

[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

 

[Domenic]

How about...

C2, copied down:

=IF(A2<>"",IF(YEAR(A2)=2008,B2,""),"")

Then...

=COUNTU(C2:C65536)

 

[Brian]

Excellent Domenic!

Much simpler than my thought.

Brian

 

[Bernd P]

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

 

[Brian]

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

 

[Brian]

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

 

[Bernd P]

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

 

[Domenic]

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

 

[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