Counting Dates

[JimS]

What's the easiest way to count the unique number of days in a list of
dates:

1/23/09
1/23/09
1/23/09
1/24/09
1/25/09
1/26/09
1/26/09
1/26/09
1/26/09

Answer should be 4.

 

[JimS]

Very slick, thanks.

If your dates are in A1:A9 the result is

=SUM(IF(FREQUENCY(A1:A9,A1:A9)>0,1))

 

[muddan madhu]

try this

=MODE(COUNTIF(A1:A9,A1:A9))

 

[Ron Rosenfeld]

try this

=MODE(COUNTIF(A1:A9,A1:A9))

Although your formula happens to give the correct result for the OP's data set,
I see no reason why it should give the correct answer to the OP's question of
getting a count of the unique number of days.

For example, changing the data set to:

1/23/2009
1/23/2009
1/23/2009
1/24/2009
1/26/2009
1/26/2009
1/26/2009
1/26/2009
1/26/2009

which has three unique days (1/23 1/24 1/26) and using your suggestion results
in an answer of five.
--ron

 

[muddan madhu]

sorry ron,

I misread the question as count of highest number of days.

 

[Ron Rosenfeld]

sorry ron,

I misread the question as count of highest number of days.

Ah -- that makes perfect sense then.
--ron

 

[JimS]

I tried using sumproduct to count the number of "Bob's" that
correspond to a date. The answer should be 4. However, I can't get
it to work. It just returns a large number string.

1/23/09 Bob
1/23/09 Tom
1/23/09 Ed
1/24/09 Bob
1/25/09 Steve
1/26/09 Bob
1/26/09 Tom
1/26/09 Mary
1/26/09 Bob

 

[Ron Rosenfeld]

I tried using sumproduct to count the number of "Bob's" that
correspond to a date. The answer should be 4. However, I can't get
it to work. It just returns a large number string.

1/23/09 Bob
1/23/09 Tom
1/23/09 Ed
1/24/09 Bob
1/25/09 Steve
1/26/09 Bob
1/26/09 Tom
1/26/09 Mary
1/26/09 Bob

Since you didn't provide your formula, I don't know why you get the result you
do. Here's an example that will give a result of 4 with your data. Note that
the Date(s) against which you are testing, as well as the Name, could be
located within a cell.

In the formula, Dates is the range where you have your list of dates (e.g.
A2:a10) and Names is the range where you have your list of Names (e.g. b2:b10)

=SUMPRODUCT((Dates>=DATE(2009,1,23))*(Dates<=DATE(2009,1,26))*(Names="Bob"))
--ron

 

[JimS]

My formula was simply:

=sumproduct((a1:a9)*(b1:b9="Bob")) which is obviously wrong.

But I have a question about your formula:

=SUMPRODUCT((Dates>=DATE(2009,1,23))*(Dates<=DATE(2009,1,26))*(Names="Bob"))

Where it says (2009,1,23)) and
(2009,1,26))

Do you always have to specify the range of dates?

 

[JimS]

I inserted your formula into my spredsheet and it worked fine. Thank
you. The formula is below.

Now what I need to do is have it count the number of instances for
"dl" for each "unique" date. In other words if there are three
entries of "dl" on 1/3/09, then I only want to count one of those.

=SUMPRODUCT((b15:b3000>=DATE(2008,4,5))*(b15:b3000<=DATE(2009,2,6))*(H15:H3000="dl"))

 

[Ron Rosenfeld]

My formula was simply:

=sumproduct((a1:a9)*(b1:b9="Bob")) which is obviously wrong.

But I have a question about your formula:

=SUMPRODUCT((Dates>=DATE(2009,1,23))*(Dates<=DATE(2009,1,26))*(Names="Bob"))

Where it says (2009,1,23)) and
(2009,1,26))

Do you always have to specify the range of dates?

You have to specify what you want.

In your post, you indicated you wanted a result of 4 and also wanted it tied to
the dates. If you only specify one date, you would only get the "Bob" on that
date.

You could also get a result of 4 if you ignore the dates completely, but that
is a consequence of the data you presented.
--ron