Forum Discussion
Automatically changing search or summing ranges
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
- Wow, thank you so much!! I've been fighting this one for hours and just going round in circles
8 Replies
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.
- Thanks Peter, that is beyond my knowledge base, i tried XLOOKUP, but failed miserably when i tried to change the summed range
Firstly, are you using Excel 365 (or 2021)?
Then, do you wish to apply the formula to more than a single row?
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))- Wow, thank you so much!! I've been fighting this one for hours and just going round in circles
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.
