Summing weekly data for a specific month

[Adar]

Hi all,

I am receiving weekly data which I try to convert to a monthly one. My challenge is to identify cells related to, for example March and sum those in one cell related to March. I know the dates when data is reported ahead of time (e.g. every Friday).

Since dataset will expand in future, manually selecting related cells is cumbersome and can lead to errors.

Example of spreadsheet is attached below:

Thank you for help.

[TABLE="width: 500"]
<tbody>[TR]
[TD]week[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

try this but be aware of weeks in which the month over takes place!

Book1
	A	B	C	D	E	F
1	week	Income			2016
2	1	300			Month	Income
3	2	6000			1-1-2016	6320
4	3	0			1-2-2016	6313
5	4	5			1-3-2016	6320
6	5	7			1-4-2016	6020
7	6	8			1-5-2016	6320
8	7	300			1-6-2016	15
9	8	6000			1-7-2016	0
10	9	0			1-8-2016	0
11	10	5			1-9-2016	0
12	11	7			1-10-2016	0
13	12	8			1-11-2016	0
14	13	300			1-12-2016	0
15	14	6000
16	15	0
17	16	5
Sheet3

Cell Formulas
Range	Formula
F3	=SUMPRODUCT(--(A:A>=WEEKNUM(E3)),--(A:A<=WEEKNUM(EOMONTH(E3,0))),B:B)
F4	=SUMPRODUCT(--(A:A>=WEEKNUM(E4)),--(A:A<=WEEKNUM(EOMONTH(E4,0))),B:B)
F5	=SUMPRODUCT(--(A:A>=WEEKNUM(E5)),--(A:A<=WEEKNUM(EOMONTH(E5,0))),B:B)
F6	=SUMPRODUCT(--(A:A>=WEEKNUM(E6)),--(A:A<=WEEKNUM(EOMONTH(E6,0))),B:B)
F7	=SUMPRODUCT(--(A:A>=WEEKNUM(E7)),--(A:A<=WEEKNUM(EOMONTH(E7,0))),B:B)
F8	=SUMPRODUCT(--(A:A>=WEEKNUM(E8)),--(A:A<=WEEKNUM(EOMONTH(E8,0))),B:B)
F9	=SUMPRODUCT(--(A:A>=WEEKNUM(E9)),--(A:A<=WEEKNUM(EOMONTH(E9,0))),B:B)
F10	=SUMPRODUCT(--(A:A>=WEEKNUM(E10)),--(A:A<=WEEKNUM(EOMONTH(E10,0))),B:B)
F11	=SUMPRODUCT(--(A:A>=WEEKNUM(E11)),--(A:A<=WEEKNUM(EOMONTH(E11,0))),B:B)
F12	=SUMPRODUCT(--(A:A>=WEEKNUM(E12)),--(A:A<=WEEKNUM(EOMONTH(E12,0))),B:B)
F13	=SUMPRODUCT(--(A:A>=WEEKNUM(E13)),--(A:A<=WEEKNUM(EOMONTH(E13,0))),B:B)
F14	=SUMPRODUCT(--(A:A>=WEEKNUM(E14)),--(A:A<=WEEKNUM(EOMONTH(E14,0))),B:B)
E4	=EOMONTH(E3,0)+1
E5	=EOMONTH(E4,0)+1
E6	=EOMONTH(E5,0)+1
E7	=EOMONTH(E6,0)+1
E8	=EOMONTH(E7,0)+1
E9	=EOMONTH(E8,0)+1
E10	=EOMONTH(E9,0)+1
E11	=EOMONTH(E10,0)+1
E12	=EOMONTH(E11,0)+1
E13	=EOMONTH(E12,0)+1
E14	=EOMONTH(E13,0)+1