SUMIFS for last 7 Days

Options
Andrea Westrich
Andrea Westrich ✭✭✭✭✭✭
edited 02/22/21 in Formulas and Functions

I'm writing a formula to sum the last 7 days of New Contacts (number field), where my date column is Day. Here is what I have:


=SUMIFS([New People:New Contacts]:[New People/New Contacts], [Day:Day], >=TODAY (-7))

The parenthesis around the -7 are pink. The parenthesis after SUMIFS and at the end are blue. I've tried a few things to no avail. What is the correct syntax?

If anyone can point me to where the troubleshooting colors are as well, I'd really appreciate it.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Try:

    =SUMIFS([New People:New Contacts]:[New People:New Contacts], [Day:Day], @cell>=TODAY(-7))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭
    Options

    Thank you Mark, but unfortunately it still did not work, I still get unparseable. It has turned the parenthesis around the -7 pink, do you know what that means?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Andrea Westrich

    You'll want to make sure your column names [in these] are exactly as your column name is spelled. It looks like your column names may not be input correctly, which is why you're receiving an error. Keep in mind that if your column only has one word (such as "day") then you won't use [these] at all.

    When a column is recognized it will highlight in a specific colour; the pink parenthesis indicates that the TODAY() function was written properly and recognized!

    Try this, assuming that your first column is called "New People:New Contacts"

    =SUMIFS([New People:New Contacts]:[New People:New Contacts], Day:Day, @cell>=TODAY(-7))

    Notice that the "Day" column reference does not have [these].

    If that first column is named something else, you'll need to update what appears [in here]. Here's an article that goes through how to reference columns in formulas: Create a Cell or Column Reference in a Formula

    Cheers!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!