Finding number of times in a month

glasgowok

New Member
Joined
Jun 28, 2012
Messages
35
I would like to be able to have the number of times a date has been entered for each month of the year recorded in a separate cell, in the sample below March would be 1 and April would be 5. Thanks in advance.

imagejpeg
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Welcome to the Mr. Excel message boards.

You did not state what version of Excel you are using.

If you are using 2007 or 2010 you can use the COUNTIFS function as follows:

Sheet6
ABC
103/05/20121
204/14/20125
304/10/2012
404/16/2012
504/16/2012
604/25/2012
7
8
9----------------------------------
10

<thead>
</thead><tbody>
</tbody>
Excel 2010

Worksheet Formulas
CellFormula
C1=COUNTIFS($A$1:$A$6,">=03/01/2012",$A$1:$A$6,"<04/01/2012")
C2=COUNTIFS($A$1:$A$6,">=04/01/2012",$A$1:$A$6,"<05/01/2012")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Hi guys, I'm using 2003, this will be over a year, it's to collect data for sickness records so in April thirty people could call in sick, May twenty five could call in.
 
Upvote 0
You could use the SUMPRODUCT function for earlier versions (pre 2007) of Excel, as well as for newer versions.

Here's and example of COUNTIFS and SUMPRODUCT side by side:

Sheet6
ABCD
103/05/201211
204/14/201255
304/10/2012
404/16/2012
504/16/2012
604/25/2012
7
8
9----------------------------------------------
10

<thead>
</thead><tbody>
</tbody>
Excel 2010

Worksheet Formulas
CellFormula
C1=COUNTIFS($A$1:$A$6,">=03/01/2012",$A$1:$A$6,"<04/01/2012")
D1=SUMPRODUCT(($A$1:$A$6>=DATE(2012,3,1))*($A$1:$A$6<DATE(2012,4,1)))
C2=COUNTIFS($A$1:$A$6,">=04/01/2012",$A$1:$A$6,"<05/01/2012")
D2=SUMPRODUCT(($A$1:$A$6>=DATE(2012,4,1))*($A$1:$A$6<DATE(2012,5,1)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
If I can make one assumption is that the cell is formatted as a date, dd/mm/yy. I would make a column next to it with the formula =month(B1). This would give you a result of 3. Then I would name the range of the month column and call it MO. Then use a countif statement =countif(MO, 3) or =countif(MO, 4). There may be a way to incorporate the two but this will definitely work.
 
Upvote 0
Hi guys, I'm using 2003, this will be over a year, it's to collect data for sickness records so in April thirty people could call in sick, May twenty five could call in.
Try this...

Book1
ABCD
21/4/2012_Jan1
32/2/2012_Feb1
44/24/2012_Mar0
55/31/2012_Apr1
66/11/2012_May1
76/20/2012_Jun3
86/25/2012_Jul0
98/29/2012_Aug1
109/23/2012_Sep1
1110/5/2012_Oct3
1210/13/2012_Nov3
1310/26/2012_Dec4
1411/23/2012___
1511/23/2012___
1611/26/2012___
1712/1/2012___
1812/8/2012___
1912/21/2012___
2012/25/2012___
Sheet1

Enter this formula in C2:

=TEXT(ROWS(C$2:C2)*29,"mmm")

Enter this formula in D2:

=SUMPRODUCT(--(TEXT(A$2:A$20,"mmm")=C2))

Select C2:D2 and copy down to C13:D13.
 
Upvote 0

Excel 2010
ABCD
128/06/2012March2
228/05/2012April1
324/06/2012May2
426/05/2012June2
501/03/2012
601/03/2012
701/04/2012
Sheet1
Cell Formulas
RangeFormula
D1=SUMPRODUCT(--(TEXT($A$1:$A$7,"mmmm")=TEXT(C1,"mmmm")))
 
Upvote 0
Thanks guys, looking at The Cmans formula which works on 2003 what happens when the year is over and it rolls into the next year in the same column? I assume it would total say two January figures, if so is there a way where I can reset to zero?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top