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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Smitty

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


Excel 2012
AB
3Row LabelsCount of Month
4Jan7
5Mar2
6Apr1
7May2
8Sep1
9Dec3
10Grand Total16
Sheet2


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

Excel 2012
ABCD
1DateMonthCount
29-JanJanuary7
310-AprFebruary0
411-JanMarch2
512-SepApril1
613-JanMay2
714-DecJune0
815-MayJuly0
916-MarAugust0
1017-JanSeptember1
1113-JanOctober0
1214-DecNovember0
1317-MayDecember3
1416-Mar
1517-Jan
1613-Jan
1714-Dec
Sheet1
Cell Formulas
RangeFormula
D2=SUMPRODUCT(--(A$2:A$100<>""),--(MONTH(A$2:A$100)=1))
D3=SUMPRODUCT(--(MONTH(A$2:A$100)=2))
D4=SUMPRODUCT(--(A$2:A$100<>""),--(MONTH(A$2:A$100)=3))
D5=SUMPRODUCT(--(A$2:A$100<>""),--(MONTH(A$2:A$100)=4))
D6=SUMPRODUCT(--(A$2:A$100<>""),--(MONTH(A$2:A$100)=5))
D7=SUMPRODUCT(--(A$2:A$100<>""),--(MONTH(A$2:A$100)=6))
D8=SUMPRODUCT(--(A$2:A$100<>""),--(MONTH(A$2:A$100)=7))
D9=SUMPRODUCT(--(A$2:A$100<>""),--(MONTH(A$2:A$100)=8))
D10=SUMPRODUCT(--(A$2:A$100<>""),--(MONTH(A$2:A$100)=9))
D11=SUMPRODUCT(--(A$2:A$100<>""),--(MONTH(A$2:A$100)=10))
D12=SUMPRODUCT(--(A$2:A$100<>""),--(MONTH(A$2:A$100)=11))
D13=SUMPRODUCT(--(A$2:A$100<>""),--(MONTH(A$2:A$100)=12))


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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,625
Messages
5,838,427
Members
430,547
Latest member
jopshio

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