Counting Dates #2

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.
0
jimx22 (226)
1/25/2009 1:31:31 AM
excel 39879 articles. 2 followers. Follow

13 Replies
995 Views

Similar Articles

[PageSpeed] 29

JimS wrote:
> 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.

If your dates are in A1:A9 the result is

=SUM(IF(FREQUENCY(A1:A9,A1:A9)>0,1))
0
smartin108 (170)
1/25/2009 1:53:08 AM
Very slick, thanks.

On Sat, 24 Jan 2009 20:53:08 -0500, smartin <smartin108@gmail.com>
wrote:

>JimS wrote:
>> 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.
>
>If your dates are in A1:A9 the result is
>
>=SUM(IF(FREQUENCY(A1:A9,A1:A9)>0,1))

0
jimx22 (226)
1/25/2009 6:43:27 AM
try this

=3DMODE(COUNTIF(A1:A9,A1:A9))



On Jan 25, 6:31=A0am, JimS <jim...@msn.com> wrote:
> 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.

0
muddanmadhu (119)
1/25/2009 7:45:36 AM
On Sat, 24 Jan 2009 23:45:36 -0800 (PST), muddan madhu <muddanmadhu@gmail.com>
wrote:

>try this
>
>=MODE(COUNTIF(A1:A9,A1:A9))
>
>
>
>On Jan 25, 6:31´┐Żam, JimS <jim...@msn.com> wrote:
>> 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.


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
0
ronrosenfeld (3122)
1/25/2009 12:44:29 PM
sorry ron,

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


On Jan 25, 5:44=A0pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Sat, 24 Jan 2009 23:45:36 -0800 (PST), muddan madhu <muddanma...@gmail=
..com>
> wrote:
>
>
>
> >try this
>
> >=3DMODE(COUNTIF(A1:A9,A1:A9))
>
> >On Jan 25, 6:31=A0am, JimS <jim...@msn.com> wrote:
> >> 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.
>
> Although your formula happens to give the correct result for the OP's dat=
a set,
> I see no reason why it should give the correct answer to the OP's questio=
n 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 re=
sults
> in an answer of five.
> --ron

0
muddanmadhu (119)
1/25/2009 1:32:58 PM
On Sun, 25 Jan 2009 05:32:58 -0800 (PST), muddan madhu <muddanmadhu@gmail.com>
wrote:

>sorry ron,
>
>I misread the question as count of highest number of days.

Ah -- that makes perfect sense then.
--ron
0
ronrosenfeld (3122)
1/25/2009 2:54:51 PM
JimS wrote:
> 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.

Here's something else that works, though I suspect it isn't perfect 
because I do not see on the web where this simple formula is offered. 
The nice thing about it is it works with text as well as numbers.

Enter as an array formula:

=SUM(1/COUNTIF(A1:A9,A1:A9))
0
smartin108 (170)
1/25/2009 10:56:14 PM
smartin wrote:
> JimS wrote:
>> 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.
> 
> Here's something else that works, though I suspect it isn't perfect 
> because I do not see on the web where this simple formula is offered. 
> The nice thing about it is it works with text as well as numbers.
> 
> Enter as an array formula:
> 
> =SUM(1/COUNTIF(A1:A9,A1:A9))

Chip Pearson points out on his site 
http://www.cpearson.com/Excel/Duplicates.aspx the above will give the 
wrong answer if the range has blank cells, so this improvement will take 
care of that:

=SUM(IF(ISBLANK(A1:A9),0,(1/COUNTIF(A1:A9,A1:A9))))
0
smartin108 (170)
1/25/2009 11:09:20 PM
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


0
jimx22 (226)
2/8/2009 9:50:02 AM
On Sun, 08 Feb 2009 01:50:02 -0800, JimS <jimx22@msn.com> wrote:

>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
0
ronrosenfeld (3122)
2/8/2009 12:03:42 PM
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?

On Sun, 08 Feb 2009 07:03:42 -0500, Ron Rosenfeld
<ronrosenfeld@nospam.org> wrote:

>On Sun, 08 Feb 2009 01:50:02 -0800, JimS <jimx22@msn.com> wrote:
>
>>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

0
jimx22 (226)
2/8/2009 2:07:25 PM
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"))


On Sun, 08 Feb 2009 07:03:42 -0500, Ron Rosenfeld
<ronrosenfeld@nospam.org> wrote:

>On Sun, 08 Feb 2009 01:50:02 -0800, JimS <jimx22@msn.com> wrote:
>
>>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

0
jimx22 (226)
2/8/2009 2:18:06 PM
On Sun, 08 Feb 2009 06:07:25 -0800, JimS <jimx22@msn.com> wrote:

>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
0
ronrosenfeld (3122)
2/8/2009 4:16:33 PM
Reply:

Similar Artilces: