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

sujith12

New Member
Joined
Jan 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.


CaptureWDAS.JPG
 

Some videos you may like

Excel Facts

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,614
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,614
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Jan 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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)))
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,614
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,614
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top