Formula for counting events

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Is there a simple formula for counting events per date?
E.g. I have a list of dates of birth of 200 kids born in 2005 and I would
like to create a table with the numbers of kids born in Jan 2005, Feb 2005,
March 2005 etc.
Please help me with a formula.
 
Hi Michael

Assuming the dates are in column A
=SUMPRODUCT(--(MONTH($A$1:$A$200=1))
will give the total for January

If you entered Jan through Dec in say E1:E12, you could put in F1
=SUMPRODUCT(--(MONTH($A$1:$A$200=ROW(F1)))
and copy down.

As you copy down, Row() will increase from 1 through 12 and give the results
for each Month.

If you had data straddling a year, say you wanted from Apr 2004 to Mar 2005,
then enter in E1:E12 proper dates for the month e.g. 01 Apr 2004, etc. but
format the cells as Format>Cells>Number>Custom> mmm
Then use the formula in F1
=SUMPRODUCT(--(TEXT($A$1:$A$200,"yymm"=TEXT(E1,"yymm"))
and copy down
 
Michael,

Suppose your list of dates are in the range A1:A100. In cells B1:B12 enter
the numbers 1 through 12, indicating months. Then, in C1, enter

=SUM(IF($A$1:$A$100=0,0,--(MONTH($A$1:$A$100)=B1)))


and press CTRL SHIFT ENTER rather than just ENTER. Copy this formula down
from C1 through C12. The formula will return the number of dates in A1:A100
that are in the month listed in column B.

Note that this is an array formula, so you must press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever you
edit it later. If you do not do this, the result will be incorrect. If you
do this properly, Excel will display the formula enclosed in curly braces
{ }. See http://www.cpearson.com/Excel/ArrayFormulas.aspx for much more
information about array formulas.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Dear Chip,
Thank you for your reply. I did exactly as you said and copied your formula
to C1. It did not work until I replaced 0,0, with 0;0; and I do not
understand why - does it depend on Excel version? I have 2003.
Could you also advice me how to do the count if I have data for more than
one year - in my case it is from May 2005 to February 2006 and then after a
pause from September 2006 to May 2007.
Kind regards,
Michael
 
Thank you Roger,
Unfortunately I get result 200 every time for your 1st and 2nd formula.
Kind regards,
Michael
 
Hi Michael

Sorry, typos on my part
They should have read

=SUMPRODUCT(--(MONTH($A$1:$A$200)=1))
and
=SUMPRODUCT(--(MONTH($A$1:$A$200)=ROW(F1)))

Also, it depends upon the dates being Excel dates and not text
representations of the date.
In another cell, type =ISNUMBER(A1) It should return TRUE if it is an Excel
date.

If you have Text values in column A, mark the whole column then
Data>Text to columns>Next>Next>Date>DMY>Finish and that will convert them to
true numeric Excel dates.
 
Back
Top