Automatically changing search or summing ranges

[TrevB]

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

[#2]

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

[#3 · Accepted solution]

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

[#4]

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

[#5]

@TrevB 

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.

[#6]

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

[#7]

@TrevB 

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

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

[#8]

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.

[#9]

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