Rolling Sumif based on weeknumber.


I am trying to program a metic that sums the total based on the previous weeknumber. This should continue automatically populate each week.

I'm thinking it looks something like:

=SUMIFS({plug}, {cut week #}, <=WEEKNUMBER(), {cut week #}, >=WEEKNUMBER(-1))

Thanks so much for your help


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Sarah_lee123

    You have a range shown in your formula that indicates weeknumber so I will assume you created a helper column to track weeknumbers? Or does that conversion still need to take place? A screenshot of your data would be helpful. I'll work from your date field

    =SUMIFS({plug}, {your date field on source sheet}, WEEKNUMBER(@cell)=WEEKNUMBER(TODAY())-1)

    If weeknumber already exist as a column in your sheet the formula becomes:

    =SUMIFS({plug}, {Weeknumber column on source sheet}, @cell=WEEKNUMBER(TODAY())-1)

    Does either of these work for you?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!