Mar 17 2022 05:45 AM
Hi, I am trying to create a formula where you enter a week number, in a separate cell and then sum a given number of weeks going forwards from that point, from another separate cell. The idea is that you can just change the values in B2 and F2 and for a given row, different ranges would be summed.
Many thanks to anyone who can help
Mar 17 2022 06:03 AM
@TrevB This one should work.
=SUM(INDEX(P6:AE6,MATCH($B$2,P$2:AE$2,0)):OFFSET(INDEX(P6:AE6,MATCH(B2,P$2:AE$2,0)),0,$F$2))
Mar 17 2022 06:09 AM
SolutionMar 17 2022 06:12 AM
@TrevB Not a very straight-forward formula, but it's was the first that came to mind. If you have Excel for MS365 or 2021, there are (most likely) a dozen other approaches to this case.
Mar 17 2022 06:15 AM
I only code for 365 so my solutions might be
= LET(
k, SEQUENCE(1,12,0),
p, XMATCH(CurrentWeek, weekNum),
s, INDEX(amount, p+k),
SUM(s)
)
= LET(
p, XLOOKUP(CurrentWeek, weekNum, amount),
q, XLOOKUP(CurrentWeek+11, weekNum, amount),
r, p:q,
SUM(r)
)
= LET(
crit1, ">="&CurrentWeek,
crit2, "<="&(CurrentWeek+11),
SUMIFS(amount, weekNum, crit1, weekNum,crit2)
)
Of those, it is SUMIFS that could be made to work in older versions of Excel.
Mar 17 2022 06:25 AM
Mar 17 2022 07:01 AM
Firstly, are you using Excel 365 (or 2021)?
Then, do you wish to apply the formula to more than a single row?
Mar 17 2022 07:07 AM
Mar 17 2022 07:14 AM
@TrevB SUMIFS had slipped my mind (thx Peter)!!
I believe you find this formula easier to work with than my original INDEX/MATCH/OFFSET monster.
=SUMIFS(P6:AF6,P$2:AF$2,">="&$B$2,P$2:AF$2,"<="&$B$2+$F$2)
Mar 17 2022 06:09 AM
Solution