Forum Discussion
Solved
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...
- Wow, thank you so much!! I've been fighting this one for hours and just going round in circles
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?
- Hi Peter,
I'm on Office 365, and I must admit i have just adjusted the ranges and used Riny's formula which has worked perfectly for what i need. Thank you for the attached example, i will have a study. thanks again for your time.