Trying to get a total form the past 7 days from this formal

Trying to get a total form the past 7 days from this formal- =SUMIFS(Date submitted:Date submitted, <TODAY(-7),[Credit Amount]:[Credit Amount])

Best Answer

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey

    Try this

    =SUMIFS([Credit Amount]:[Credit Amount], [Date submitted]:[Date submitted], >=TODAY(-7), [Date submitted]:[Date submitted], <=TODAY())

    cheers,

    Kelly

  • Joey Casullo
    Joey Casullo ✭✭✭✭
    edited 02/12/21

    Thanks Kelly, You rock😎

    How about if I wanted to count the number of entries I have for the past 7 days

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey Joey

    Thanks. I chose the above version of the formula so the syntax of the SumIfs formula was easily seen - range to be summed, range1, criteria1, range2, criteria2, range2, criteria3, etc.

    If you preferred, a more streamlined formula (with same syntax)is

    =SUMIFS([Credit Amount]:[Credit Amount], [Date submitted]:[Date submitted], AND(@cell>=TODAY(-7), @cell <=TODAY()))

    Thanks for contributing your question to the community.

    cheers

  • Joey Casullo
    Joey Casullo ✭✭✭✭

    I am sorry, I was not clear. I am looking for the total number of rows, just a basic number of entries (rows) I have? The answer is 3, how do I get that



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey

    Sorry. Since you used the SUMIFs I assumed you wanted a sum. To get a Count when you have criteria, we use COUNTIFs.

    COUNTIFs has the syntax range1, criteria1, range2, criteria2, etc

    We can take the SUMIFs formula and get rid of that Sum range in the beginning to give us the COUNTIFS formula.

    COUNTIFS([Date submitted]:[Date submitted], AND(@cell>=TODAY(-7), @cell <=TODAY()))


    cheers,

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!