Calendar calculation

[Manolocs]

Hi, I have column A = all 2015 calendar,
from columns E to Y are the number of students in each place each day.
Column AB is the total of students each month on each place to calculate the total gain.
I set the formula =SUM(E3:E33) to give me the total number of students of Place 1 in January, but to copy and past for all months/years does not past in the correct dates, so my question: Is there a way to make the calculations for month I mean from 1st to 31st of January, then from 1st to 28th of February and so on?
Thanks in advance.

[/IMG]

 

[MarcelBeug]

Try the following array formula in AB4, confirm with Ctrl+Shift+Enter, not just Enter:

=SUM(IF(MONTH($A$3:$A$368)=MONTH($A4),IF($E$1:$X$1=$N4,$E$3:$X$368)))

 

[ukmikeb]

Hi

Try :-
Manoloc

	A	B	C	D	E	F	G	H	I	AA	AB
2					1 Place	2 Place	3 Place				Students
3	01-Jan-15				1					1 Place	45
4	02-Jan-15									2 Place	33
5	03-Jan-15					3				3 Place	37
6	04-Jan-15
7	05-Jan-15				2		4
8	06-Jan-15
9	07-Jan-15					4
10	08-Jan-15
11	09-Jan-15
12	10-Jan-15				3		8
13	11-Jan-15
14	12-Jan-15					5
15	13-Jan-15
16	14-Jan-15
17	15-Jan-15						7
18	16-Jan-15				4
22	20-Jan-15				5		5
23	21-Jan-15
24	22-Jan-15					6
25	23-Jan-15
26	24-Jan-15				6
28	26-Jan-15				7		3
29	27-Jan-15					7
30	28-Jan-15				8
33	31-Jan-15				9	8	10				Students
34	01-Feb-15				1	4	7			1 Place	6
35	02-Feb-15				2	5	8			2 Place	15
36	03-Feb-15				3	6	9			3 Place	24

<thead>
</thead><tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula AB3 =SUMPRODUCT(--(MONTH($A$3:$A$370)=MONTH($A3)),--(OFFSET($D$3:$D$370,,DAY($A3)))) AB4 =SUMPRODUCT(--(MONTH($A$3:$A$370)=MONTH($A4)),--(OFFSET($D$3:$D$370,,DAY($A4)))) AB5 =SUMPRODUCT(--(MONTH($A$3:$A$370)=MONTH($A5)),--(OFFSET($D$3:$D$370,,DAY($A5)))) AB34 =SUMPRODUCT(--(MONTH($A$3:$A$370)=MONTH($A34)),--(OFFSET($D$3:$D$370,,DAY($A34)))) AB35 =SUMPRODUCT(--(MONTH($A$3:$A$370)=MONTH($A35)),--(OFFSET($D$3:$D$370,,DAY($A35)))) AB36 =SUMPRODUCT(--(MONTH($A$3:$A$370)=MONTH($A36)),--(OFFSET($D$3:$D$370,,DAY($A36)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Note the suggestion that you align the totals grid with the start day of each month. That way there is one formula for all your "place" totals.

hth

 

[Manolocs]

@Mike that was exactly what I needed thank you very much Mike