Finding number of times in a month

[glasgowok]

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.

 

[Robert Mika]

One way would be:

Excel Workbook
	A	B	C	D
1	28/06/2012	March	2011	2
2	28/05/2012	March	2012	2
3	24/06/2012	may	2012	2
4	26/05/2012	June	2012	2
5	01/03/2012	*	*	*
6	01/03/2012	*	*	*
7	01/04/2012	*	*	*
8	01/03/2011	*	*	*
9	01/03/2011	*	*	*
Sheet3

or
Sheet3

*	A	B	C
1	28/06/2012	March	0
2	28/05/2012	may	2
3	24/06/2012	June	2
4	26/05/2012	*	*
5	01/04/2012	*	*
6	01/03/2011	*	*
7	01/03/2011	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:92px;"><col style="width:64px;"><col style="width:64px;"></colgroup>

Spreadsheet Formulas

Cell Formula C1 =SUMPRODUCT(--(TEXT($A$1:$A$7,"mmmm")=TEXT(B1,"mmmm")),--(YEAR($A$1:$A$7)=YEAR(TODAY()))) C2 =SUMPRODUCT(--(TEXT($A$1:$A$7,"mmmm")=TEXT(B2,"mmmm")),--(YEAR($A$1:$A$7)=YEAR(TODAY()))) C3 =SUMPRODUCT(--(TEXT($A$1:$A$7,"mmmm")=TEXT(B3,"mmmm")),--(YEAR($A$1:$A$7)=YEAR(TODAY())))

 

[T. Valko]

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?

Do you have a specific time frame you're working with? For example, start in March 2012 and end in ___?

 

[glasgowok]

Just indoors so not had a chance to look at the above solutions but to answer your question, the data I'm working on goes back several years but it's all to being cleaned up and the new work sheet will start from the start of this financial year and the coloumn that the dates are input into will roll on year after year.

 

[T. Valko]

Just indoors so not had a chance to look at the above solutions but to answer your question, the data I'm working on goes back several years but it's all to being cleaned up and the new work sheet will start from the start of this financial year and the coloumn that the dates are input into will roll on year after year.

Ok, so what date is the start of your financial year?

 

[glasgowok]

01/04/12 ( uk) I'm really a novice at Excel but it's becoming more and more important to my job.

 

[Oeldere]

Or with a pivot table.

Excel Workbook
	A	B	C
1	date	month	year
2	28-5-2012	5	2012
3	24-6-2012	6	2012
4	26-5-2012	5	2012
5	1-4-2012	4	2012
6	1-3-2012	3	2012
7	1-3-2011	3	2011
8	1-3-2011	3	2011
Blad1

Cell Formulas
Range	Formula
B2	=MONTH(A2)
B3	=MONTH(A3)
B4	=MONTH(A4)
B5	=MONTH(A5)
B6	=MONTH(A6)
B7	=MONTH(A7)
B8	=MONTH(A8)
C2	=YEAR(A2)
C3	=YEAR(A3)
C4	=YEAR(A4)
C5	=YEAR(A5)
C6	=YEAR(A6)
C7	=YEAR(A7)
C8	=YEAR(A8)

count month	Collumnlabels
Rijlabels	2011	2012	Endtotal
3	2	1	3
4		1	1
5		2	2
6		1	1
Endtotal	2	5	7

<TBODY> </TBODY>

 

[T. Valko]

01/04/12 ( uk) I'm really a novice at Excel but it's becoming more and more important to my job.

Try this...

A2:A100 = dates

Enter this formula in C2:

=TEXT(DATE(2012,4+ROWS(C$2:C2)-1,1),"mmm yyyy")

Enter this formula in D2:

=SUMPRODUCT(--(TEXT(A$2:A$100,"mmm yyyy")=C2))

Select C2:D2 and copy down as needed.

 

[glasgowok]

Thanks guys it works!

 

[T. Valko]

Thanks guys it works!

Good deal. Thanks for the feedback!