Forum Discussion

TrevB's avatar
TrevB
Copper Contributor
Mar 17, 2022
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...
Formulas and Functions
  • TrevB's avatar
    TrevB
    Mar 17, 2022
    Wow, thank you so much!! I've been fighting this one for hours and just going round in circles
TrevB's avatar
TrevB
Copper Contributor
Mar 17, 2022
Thanks Peter, that is beyond my knowledge base, i tried XLOOKUP, but failed miserably when i tried to change the summed range
  • TrevB's avatar
    TrevB
    Copper Contributor
    Mar 17, 2022
    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.
    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Mar 17, 2022

      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)

       

Resources