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:

Excel 2012
	A	B
3	Row Labels	Count of Month
4	Jan	7
5	Mar	2
6	Apr	1
7	May	2
8	Sep	1
9	Dec	3
10	Grand Total	16
Sheet2

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]

Excel 2012
	A	B	C	D
1	Date		Month	Count
2	9-Jan		January	7
3	10-Apr		February	0
4	11-Jan		March	2
5	12-Sep		April	1
6	13-Jan		May	2
7	14-Dec		June	0
8	15-May		July	0
9	16-Mar		August	0
10	17-Jan		September	1
11	13-Jan		October	0
12	14-Dec		November	0
13	17-May		December	3
14	16-Mar
15	17-Jan
16	13-Jan
17	14-Dec
Sheet1

Cell Formulas
Range	Formula
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.

 

[Paywand]

Thanks a lot, it's Graet ! Worked