Forum Discussion

Jacob_PWR's avatar
Jacob_PWR
Copper Contributor
Feb 08, 2022

Sum columns based on date

Looking for some help creating a sum formula that updates based on a date - stumped on this one. In the example I have created a "Week Start Date" (cell A16) represents the first Sunday of each week and corresponds with the columns in row 1.

 

In cells AW21 and AW22 I am trying to create a 13 week "rolling" sum that calculates based on the current date. For example on 2/8/22 it would sum the weeks of data 2/6/22 - 5/1/22. Next week (2/13/22) it would sum 2/13/22 --> 5/8/22.

 

Is there any way to do this? Thanks in advance for help.

 

 

 

Jacob 

excel
Formulas and Functions

1 Reply

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Feb 09, 2022

    Hi Jacob_PWR 

     

    I give it a try. Here is my solution:

    =SUMIFS(B2:R2;B1:R1;">="&(B5-WEEKDAY(B5)+1);B1:R1;"<="&(B5-WEEKDAY(B5)+5*7))

     

    For demonstration purposes, it covers just 5 weeks, therefore the +5*7 at the end of the formula. For 13 weeks just change to 13*7 (or  91)

     

    I added my example file, so you can easier discover.

     

     

Resources