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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

glasgowok

New Member
Joined
Jun 28, 2012
Messages
35
In case the image doesn't appear
5/3/12
14/4/12
10/4/12
16/4/12
16/4/12
25/4/12


 

Jeff Meyers

Active Member
Joined
Mar 14, 2012
Messages
405
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>
 

glasgowok

New Member
Joined
Jun 28, 2012
Messages
35

ADVERTISEMENT

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.
 

Jeff Meyers

Active Member
Joined
Mar 14, 2012
Messages
405
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>
 

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514

ADVERTISEMENT

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.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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...

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:86px;" /><col style="width:29px;" /><col style="width:37px;" /><col style="width:37px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; border-style:solid; border-width:1px; border-color:#000000; ">1/4/2012</td><td style="color:#ffffff; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Jan</td><td style="text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2/2/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Feb</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">4/24/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Mar</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">5/31/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Apr</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">6/11/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">May</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">6/20/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Jun</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">6/25/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Jul</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">8/29/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Aug</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">9/23/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Sep</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">10/5/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Oct</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">10/13/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Nov</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">10/26/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Dec</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">4</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">11/23/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">11/23/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">11/26/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">12/1/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">12/8/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">12/21/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">12/25/2012</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></table> <br /><br />
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.
 

TheCman81

Well-known Member
Joined
Feb 28, 2012
Messages
535
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">28/06/2012</td><td style="text-align: right;;"></td><td style=";">March</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">28/05/2012</td><td style="text-align: right;;"></td><td style=";">April</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">24/06/2012</td><td style="text-align: right;;"></td><td style=";">May</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">26/05/2012</td><td style="text-align: right;;"></td><td style=";">June</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">01/03/2012</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">01/03/2012</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">01/04/2012</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$A$1:$A$7,"mmmm"</font>)=TEXT(<font color="Green">C1,"mmmm"</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

glasgowok

New Member
Joined
Jun 28, 2012
Messages
35
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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
Top