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

#### sujith12

##### New Member
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.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

##### Well-known Member
Welcomr to MrExcel Message Board.

##### Well-known Member
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

Excel Formula:
``=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

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

example file.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2WeeksWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11Week 12Week 13Week 14Week 15Week 16Week 17Week 18Week 19Week 20
3Revenue\$ 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
5Weeks In Month454444454445
6MonthJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
7Team 1#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!
8Team 2
9Team 3
10Team 4
Sheet1
Cell Formulas
RangeFormula
B7:C7B7=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:M7D7=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)))

##### Well-known Member

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:
Excel 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
ABCDEFGHIJKLMNOPQRSTU
1
2Weeks1234567891011121314151617181920
3Revenue10000150002000025000300003500040000450005000055000600006500070000750008000085000900009500050009000
4
5Weeks In Month454444454445
6MonthJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
7Team 11000001700002500003300001090000000000
8Team 2
9Team 3
10Team 4
Sheet1
Cell Formulas
RangeFormula
B7:M7B7=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

##### New Member
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.

##### Well-known Member
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
Excel Formula:
``=SUMIFS(\$B\$3:\$BC\$3,\$B\$2:\$BC\$2,">"&SUM(\$A\$5:A5),\$B\$2:\$BC\$2,"<="&SUM(\$A\$5:B5))``

Replies
6
Views
89
Replies
3
Views
538
Replies
1
Views
225
Replies
1
Views
182
Replies
1
Views
62

1,127,391
Messages
5,624,441
Members
416,028
Latest member
aej

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back