How to count dates with multiple values?
 

(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....

How to count dates with multiple values?
 

One way:

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

Based on your sample the result is 5.

--
Biff
Microsoft Excel MVP


"dj479794" wrote in message
...
(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....

How to count dates with multiple values?
 

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

"T. Valko" wrote:

One way:

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

Based on your sample the result is 5.

--
Biff
Microsoft Excel MVP


"dj479794" wrote in message
...
(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....

How to count dates with multiple values?
 

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


"dj479794" wrote in message
...
This array gave me an ouptut of zero. I must be doing something wrong. Any
ideas based on a result of zero?

"T. Valko" wrote:

One way:

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

Based on your sample the result is 5.

--
Biff
Microsoft Excel MVP


"dj479794" wrote in message
...
(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....

How to count dates with multiple values?
 

I changed it to my range A3:A707 replaced A1:A8

"Peo Sjoblom" wrote:

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


"dj479794" wrote in message
...
This array gave me an ouptut of zero. I must be doing something wrong. Any
ideas based on a result of zero?

"T. Valko" wrote:

One way:

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

Based on your sample the result is 5.

--
Biff
Microsoft Excel MVP


"dj479794" wrote in message
...
(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....

How to count dates with multiple values?
 

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


--
Biff
Microsoft Excel MVP


"dj479794" wrote in message
...
I changed it to my range A3:A707 replaced A1:A8

"Peo Sjoblom" wrote:

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


"dj479794" wrote in message
...
This array gave me an ouptut of zero. I must be doing something wrong.
Any
ideas based on a result of zero?

"T. Valko" wrote:

One way:

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

Based on your sample the result is 5.

--
Biff
Microsoft Excel MVP


"dj479794" wrote in message
...
(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....

How to count dates with multiple values?
 

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. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"dj479794" wrote in message
...
I changed it to my range A3:A707 replaced A1:A8

"Peo Sjoblom" wrote:

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


"dj479794" wrote in message
...
This array gave me an ouptut of zero. I must be doing something wrong.
Any
ideas based on a result of zero?

"T. Valko" wrote:

One way:

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

Based on your sample the result is 5.

--
Biff
Microsoft Excel MVP


"dj479794" wrote in message
...
(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....

How to count dates with multiple values?
 

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.

--
Biff
Microsoft Excel MVP


"dj479794" wrote in message
...
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. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"dj479794" wrote in message
...
I changed it to my range A3:A707 replaced A1:A8

"Peo Sjoblom" wrote:

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


"dj479794" wrote in message
...
This array gave me an ouptut of zero. I must be doing something
wrong.
Any
ideas based on a result of zero?

"T. Valko" wrote:

One way:

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

Based on your sample the result is 5.

--
Biff
Microsoft Excel MVP


"dj479794" wrote in message
...
(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....