How to count dates with multiple values?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

(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....
 
One way:

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

Based on your sample the result is 5.
 
This array gave me an ouptut of zero. I must be doing something wrong. Any
ideas based on a result of zero?
 
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
 
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))
 
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!
 
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.
 
Back
Top