How to count dates with multiple values?

D

dj479794

(Excel 2003)

I have dates in a column. Some days from start to end are missing and some
show up multiple times. I just want a count of how may days are in the list
without counting the same date more than once or counting dates that are not
in the list.


7/1/07
7/2/07
7/2/07
7/2/07
7/4/07
7/4/07
9/13/07
9/15/07....
 
T

T. Valko

One way:

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

Based on your sample the result is 5.
 
D

dj479794

This array gave me an ouptut of zero. I must be doing something wrong. Any
ideas based on a result of zero?
 
P

Peo Sjoblom

Are you using the correct range, did you just copy Biff's formula which
count A1:A8 and while your values are somewhere else?


--


Regards,


Peo Sjoblom
 
T

T. Valko

Does the formula still return 0?

If so, hmmm....

Try this one. This one will count NUMBERS only.

=COUNT(1/FREQUENCY(A3:A707,A3:A707))
 
D

dj479794

Ok. that is wierd. Your second formula worked. but the dates are stored as
dates and not numbers. oh well. it works thats whats matter.

THANKS!
 
T

T. Valko

Dates are really just numbers formatted to look like dates.

For example, the date: 9/25/2007 has an underlying value of 39350.

The first formula I suggested will count *all* uniques, both TEXT and
NUMBERS.

The second formula I suggested will count *only* unique numbers.

Either formula should work. Can't understand why the first one returns a 0.
 
Top