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[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]AA[/TH]
[TH]AB[/TH]
[/TR]
</thead><tbody>[TR]
[TH]2[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1 Place[/TD]
[TD]2 Place[/TD]
[TD]3 Place[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Students[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]01-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1 Place[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: right"]02-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2 Place[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]03-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3 Place[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"]04-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="align: right"]05-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]06-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD="align: right"]07-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD="align: right"]08-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD="align: right"]09-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD="align: right"]10-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD][/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD="align: right"]11-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD="align: right"]12-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD="align: right"]13-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]16[/TH]
[TD="align: right"]14-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]17[/TH]
[TD="align: right"]15-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]18[/TH]
[TD="align: right"]16-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]22[/TH]
[TD="align: right"]20-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]23[/TH]
[TD="align: right"]21-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]24[/TH]
[TD="align: right"]22-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]25[/TH]
[TD="align: right"]23-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]26[/TH]
[TD="align: right"]24-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]28[/TH]
[TD="align: right"]26-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]29[/TH]
[TD="align: right"]27-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]30[/TH]
[TD="align: right"]28-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]33[/TH]
[TD="align: right"]31-Jan-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Students[/TD]
[/TR]
[TR]
[TH]34[/TH]
[TD="align: right"]01-Feb-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]1 Place[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TH]35[/TH]
[TD="align: right"]02-Feb-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]2 Place[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TH]36[/TH]
[TD="align: right"]03-Feb-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]3 Place[/TD]
[TD="align: right"]24[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

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

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]



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