Count the how many different values in a range

B

beechum1

in a given range, a1:a50, if i have 5,4,3,2 or 1 different date,
(formatted as 1-Jan if relevent) I want to know how many different
dates there are.

1-Jan
2-Jan
3-Jan
5-Jan
for instance would return a value of 4

1-Jan
5-Jan
would return a value of 2.

I just don't know what statement to even start with.....
 
B

Biff

Hi!

If you want the count of ALL dates:

=COUNT(A1:A50)

If you want the count of unique dates:

1-Jan
1-Jan
1-Jan
5-Jan

Result would be 2:

=SUMPRODUCT((A1:A50<>"")/COUNTIF(A1:A50,A1:A50&""))

Biff
 
B

Biff

Hmmm........

Or, maybe you only want to count the dates if they are the 1st, 2nd, 3rd,
4th or 5th of the month:

Month and year specific: (for Jan 2006 only)

=SUMPRODUCT(--(A1:A50>=DATE(2006,1,1)),--(A1:A50<=DATE(2006,1,5)))

For any month/year (1900 and up)

=SUMPRODUCT(--(DAY(A1:A50)>=1),--(DAY(A1:A50)<=5))

Biff
 
B

beechum1

nm, I found the group of threads on the countu formula, which i'm still
a little confused on... was that taken out of 2003? or was it every
available???

Seems like a handy formula to have.
 
Top