 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 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 8 Replies

# Re: Automatically changing search or summing ranges

@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))``
best response confirmed by TrevB (New Contributor)
Solution

# Re: Automatically changing search or summing ranges

Wow, thank you so much!! I've been fighting this one for hours and just going round in circles

# Re: Automatically changing search or summing ranges

@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.

# Re: Automatically changing search or summing ranges

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.

# Re: Automatically changing search or summing ranges

Thanks Peter, that is beyond my knowledge base, i tried XLOOKUP, but failed miserably when i tried to change the summed range

# Re: Automatically changing search or summing ranges

Firstly, are you using Excel 365 (or 2021)?

Then, do you wish to apply the formula to more than a single row?

# Re: Automatically changing search or summing ranges

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.

# Re: Automatically changing search or summing ranges

@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)``