SUMIFs for last 7 days

Andrea Westrich
Andrea Westrich ✭✭✭✭✭✭
edited 03/02/21 in Formulas and Functions

I would like to sum the last 7 days of a column on a daily basis. No matter what day it is, I want to know what the sum of the last 7 days is. I'm using this formula in a summary: =SUMIFS([Workshop Signups]:[Workshop Signups], Day:Day, TODAY(-7)).

When I verified (tested) the numbers, it totals the last 6 days, including today. If I change the 7, the sum does not change until I put in a -10. At that point the sum of the last 7 days is correct. I tested this by putting different numbers in the column for 10 days.

Am I doing something wrong or can someone explain how the TODAY function works with negative numbers?

Thank you for your help!


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    When using minus numbers it should only be counting the date that was 7 days earlier than today. Try this variation and see if you get a total count...

    =SUMIFS([Workshop Signups]:[Workshop Signups], Day:Day, AND(@cell >TODAY(-7), @cell <=Today()))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!