How to calculate/recalculate/auto-update formula for SUM based on 2 conditions

[sujith12]

Hello Everyone,

I have an interesting problem where I have --

Table 1 with 1 row containing revenue forecast values and 20 columns (1 for each week therefore 20 weeks) and
Table 2 with 6 rows & 12 columns in which the first row has 2 numbers either 4 or 5 which denotes the number of weeks that particular month has and the 2nd row contains months (Jan - Dec) and the remaining 4 rows are for 4 teams

We have pre-defined each month to have a set number of weeks.

I am looking to calculate the forecasts based on the condition if, for example, January month is a 4 week & February a 5 week month, then it checks the first row to see if its a 4 or 5 and then the sum formula will select the appropriate number of weeks from table 1. This should ideally also account for the fact that if a month is 5, then the subsequent month formula is also updated to not include that week 5 value in its sum.

Hopefully, this formula setup will also account for the fact that if we cut any cells from Jan as an example to account to team 2 starting in March, then the formula auto-updates.

 

[maabadi]

Welcomr to MrExcel Message Board.
Please Upload Example file & Desired Results with XL2BB ADDIN (Preferable) or upload at sites www.Dropbox.com , GoogleDrive OR OneDrive and Insert Link here.

 

[maabadi]

1. Change $E$6:$R$6 to your total sum range and fix it.
2. Change $E$5:$R$5 to your Week number range, from week1 to last

=SUMIFS($E$6:$R$6,$E$5:$R$5,">="&WEEKNUM(DATE(YEAR(TODAY()),ROWS($E$5:E5),1)),$E$5:$R$5,"<"&WEEKNUM(EOMONTH(DATE(YEAR(TODAY()),ROWS($E$5:E5),1),0)))

 

[sujith12]

Hello,
Thank you for your reply. I tried the formula provided but it did not work. Below is the example file

example file.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	Weeks	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20
3	Revenue	$ 10,000	$ 15,000	$ 20,000	$ 25,000	$ 30,000	$ 35,000	$ 40,000	$ 45,000	$ 50,000	$ 55,000	$ 60,000	$ 65,000	$ 70,000	$ 75,000	$ 80,000	$ 85,000	$ 90,000	$ 95,000	$ 5,000	$ 9,000
4
5	Weeks In Month	4	5	4	4	4	4	4	5	4	4	4	5
6	Month	January	February	March	April	May	June	July	August	September	October	November	December
7	Team 1	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
8	Team 2
9	Team 3
10	Team 4
Sheet1

Cell Formulas
Range	Formula
B7:C7	B7	=SUMIFS(#REF!,$B$5:$M$5,">="&WEEKNUM(DATE(YEAR(TODAY()),ROWS($B$5:$M$5),1)),$E$3:$R$3,"<"&WEEKNUM(EOMONTH(DATE(YEAR(TODAY()),ROWS(C$3:$E3),1),0)))
D7:M7	D7	=SUMIFS(#REF!,$B$5:$M$5,">="&WEEKNUM(DATE(YEAR(TODAY()),ROWS($B$5:$M$5),1)),$E$3:$R$3,"<"&WEEKNUM(EOMONTH(DATE(YEAR(TODAY()),ROWS($E$3:E3),1),0)))

 

[maabadi]

1. Change Week1 to Week 20 to 1 to 20
2. You don't updated your cell references at formula
3. You have #REF! at your formula . correct it.
It calculate week in month also. Don't Need row 5
This is formula:

=SUMIFS($B$3:$BC$3,$B$2:$BC$2,">="&WEEKNUM(DATE(YEAR(TODAY()),COLUMNS($B$2:B2),1)),$B$2:$BC$2,"<"&WEEKNUM(EOMONTH(DATE(YEAR(TODAY()),COLUMNS($B$2:B2),1),0)))

Book21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	Weeks	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
3	Revenue	10000	15000	20000	25000	30000	35000	40000	45000	50000	55000	60000	65000	70000	75000	80000	85000	90000	95000	5000	9000
4
5	Weeks In Month	4	5	4	4	4	4	4	5	4	4	4	5
6	Month	January	February	March	April	May	June	July	August	September	October	November	December
7	Team 1	100000	170000	250000	330000	109000	0	0	0	0	0	0	0
8	Team 2
9	Team 3
10	Team 4
Sheet1

Cell Formulas
Range	Formula
B7:M7	B7	=SUMIFS($B$3:$BC$3,$B$2:$BC$2,">="&WEEKNUM(DATE(YEAR(TODAY()),COLUMNS($B$2:B2),1)),$B$2:$BC$2,"<"&WEEKNUM(EOMONTH(DATE(YEAR(TODAY()),COLUMNS($B$2:B2),1),0)))

 

[sujith12]

Thank you so much for your help but this is not what I was looking for.

Let me try to explain differently -

If Weeks in Month = 4, then the formula should sum the values under Weeks 1, 2, 3, 4 i.e., B3:E3
If Weeks in Month = 5, then the formula should sum the values under Weeks 1, 2, 3, 4 i.e., B3:F3

Likewise, if you look at August which is 5, then September is 4, it should also have this logic where it checks the previous month's weeks if it is 5 then it does not double count values from the above revenue rows.

Hope this clears things up.

 

[maabadi]

I think you have month sum separately. if Jan is 4 sum of week 1 to 4, then if Feb is 5 sum of week 5 to 9 and etc...
Then Use this formula at B7 & drag it right

=SUMIFS($B$3:$BC$3,$B$2:$BC$2,">"&SUM($A$5:A5),$B$2:$BC$2,"<="&SUM($A$5:B5))