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

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


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:


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



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:


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.


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


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

I just found another way of doing it...


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.