Count each of month

[Paywand]

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.

 

[Smitty]

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]

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]

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]

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]

<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.

 

[Paywand]

Thanks a lot, it's Graet ! Worked