MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting number of occurrences of a date


Posted by Jennifer on August 17, 2001 3:33 PM

I have a column of dates spanning over several years (format MM/DD/YY). What I'm trying to do is calculate how many times an occurrence happens in a month.

Like, how many entries I have for January 2001, February 2001, April 2002, etc.

I've tried using the COUNTIF function, and I can get it to calculate how many entries I have AFTER 01/01/01, but how can I have it to calculate how many entries i have between 1/01 and 1/31/01?

That way I can make a simple graph showing how many times things happened in a month.

Please email all responses to jennfurr@yahoo.com


Posted by Aladin Akyurek on August 17, 2001 3:45 PM

Jennifer,

You have a case of what I like to call multiconditional count. COUNTIF is used in case you have a single condition.

Supposing that you have the date entries in A2:A600.

Put 1/01/01 in say B1 and 1/31/01 in B2.

In B3 enter: =SUMPRODUCT((A2:A600>=B1)*(A2:A600<=B2))

I interpreted "between" as "Inclusive between".

Equivalently, you can use the following formula, if you so desire:

=SUM((A2:A600>=B1)*(A2:A600<=B2)),

which must be entered by hitting CONTROL+SHIFT+ENTER at the same time (not just ENTER).

Aladin

=============

Posted by Jennifer on August 17, 2001 4:18 PM

Cool, Thanks

I got the second formula to work fine. The first one kept returning zeroes. Oh well, I got the answer I needed.

Now, for a followup question that's probably been answered a jillion times. What's up with the Ctrl+Shift+Enter? Why not just plain Enter?

Posted by Aladin Akyurek on August 17, 2001 4:50 PM

Re: Cool, Thanks

The first one should work too. Even unchanged. Try however the following:

=SUMPRODUCT(((A2:A600>=B1)*(A2:A600<B2))+0)

Oh well, I got the answer I needed.

The second formula is an array formula. Control+shift+enter tells Excel that the formula must be treated as such.

Activate the cell where you placed this formula, go to the Formula Bar, select a term (an subexpression within formula, e.g., (A2:A600=B1) and hit F9. Do this for each term, you'll probably see how such a formula works: by creating arrays of values. When ready, hit ESCAPE. Otherwise you'll loose the formula.

Aladin

PS. Make that SUMPRODUCT formula work.

================================


You can use the above proc with SUMPRODUCT and other formulas.

===========

Posted by Jennifer on August 20, 2001 7:57 AM

Re: Cool, Thanks

Heh, got the SUMPRODUCT one to work too! Thanks Aladin - you're a GENIUS... or is that =GENIUS(A1:A100)?

jen

Posted by Jennifer on August 29, 2001 4:50 PM

I just found another way of doing it...

=FREQUENCY(A2:A2000,J2:J30)

Where A2:A2000 is my data, and J2:J30 is the upper range of the dates that i wanted to count (i.e., 12/31/74, 12/31/75, etc).

And it's an array formula too! Uhoh, I'm turning into an excel geek!

Is there any advantage/drawback of doing it one way vs the other? SUM, SUMPRODUCT, and FREQUENCY all seem to work fine.