Count each of month

Paywand

New Member
Joined
Apr 18, 2014
Messages
39
Dear all,

i hope you are doing well.

i work on an excel sheet, in their has a lot of date, so i want to count each of month which has in their.

for example if in their has each below date (15 May - 17 May ) in one cell count (3 May), because their is 2 of month May.

Date
09 January
10 April
11 January
12 September
13 January
14 December
15 May
16 March
17 January
13 January
14 December
17 May
16 March
17 January
13 January
14 December

<colgroup><col></colgroup><tbody>
</tbody>

thanks in advance.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
You can do it with a Pivot Table using Group by Month:

<b>Excel 2012</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 /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Row Labels</td><td style=";">Count of Month</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Jan</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Mar</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Apr</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">May</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Sep</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Dec</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Grand Total</td><td style="text-align: right;;">16</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">Sheet2</p><br /><br />

HTH,
 

Paywand

New Member
Joined
Apr 18, 2014
Messages
39
could you please let me know how to do that, but be aware their date include days and months, i want to count only month which same.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Create a Pivot Table and put Date in the Row field & Values field, where it should be a Count. Then right-click on A4 and select Group By-->Months.
 

Paywand

New Member
Joined
Apr 18, 2014
Messages
39

ADVERTISEMENT

sorry but its not able with my data, because there is a lot of date, and i want to count them in 12 cell each one for one month, and count them in own cell.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
<b>Excel 2012</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="color: #574123;background-color: #FFFFFF;;">Date</td><td style="text-align: right;;"></td><td style=";">Month</td><td style=";">Count</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #574123;background-color: #FFFFFF;;">9-Jan</td><td style="text-align: right;;"></td><td style=";">January</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #574123;background-color: #FFFFFF;;">10-Apr</td><td style="text-align: right;;"></td><td style=";">February</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #574123;background-color: #FFFFFF;;">11-Jan</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;">5</td><td style="color: #574123;background-color: #FFFFFF;;">12-Sep</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;">6</td><td style="color: #574123;background-color: #FFFFFF;;">13-Jan</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;">7</td><td style="color: #574123;background-color: #FFFFFF;;">14-Dec</td><td style="text-align: right;;"></td><td style=";">June</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #574123;background-color: #FFFFFF;;">15-May</td><td style="text-align: right;;"></td><td style=";">July</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="color: #574123;background-color: #FFFFFF;;">16-Mar</td><td style="text-align: right;;"></td><td style=";">August</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="color: #574123;background-color: #FFFFFF;;">17-Jan</td><td style="text-align: right;;"></td><td style=";">September</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="color: #574123;background-color: #FFFFFF;;">13-Jan</td><td style="text-align: right;;"></td><td style=";">October</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="color: #574123;background-color: #FFFFFF;;">14-Dec</td><td style="text-align: right;;"></td><td style=";">November</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="color: #574123;background-color: #FFFFFF;;">17-May</td><td style="text-align: right;;"></td><td style=";">December</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="color: #574123;background-color: #FFFFFF;;">16-Mar</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;">15</td><td style="color: #574123;background-color: #FFFFFF;;">17-Jan</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;">16</td><td style="color: #574123;background-color: #FFFFFF;;">13-Jan</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;">17</td><td style="color: #574123;background-color: #FFFFFF;;">14-Dec</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">D2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$100<>""</font>),--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$100<>""</font>),--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=3</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D5</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$100<>""</font>),--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=4</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$100<>""</font>),--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=5</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D7</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$100<>""</font>),--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=6</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$100<>""</font>),--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=7</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D9</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$100<>""</font>),--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=8</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D10</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$100<>""</font>),--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=9</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D11</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$100<>""</font>),--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=10</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D12</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$100<>""</font>),--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=11</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D13</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$100<>""</font>),--(<font color="Red">MONTH(<font color="Green">A$2:A$100</font>)=12</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

EDIT: oops, the formula for February should be the same as the rest.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,063
Messages
5,622,470
Members
415,897
Latest member
Barry18180

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