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.
