SUMIFS and Exclusion formula needed

Options
Ryan Brock
Ryan Brock ✭✭
edited 02/12/21 in Formulas and Functions

Hello,

I am very green to SmartSheet and hoping to find some help here... I am looking to write a formula referencing another sheet that will spit out the total (SUM) from one column, provided (IF) another column is a certain value, and yet another column (IFS) is NOT a certain value. I'm having trouble figuring out how to "exclude if."

Here is what I have. Please help me clean it up! :)

=SUMIFS({2021 YTD Budget : Existing/Innovation}, "Existing", {2021 YTD Budget : PO#}, NOT(@cell = "TBD"), {2021 YTD Budget : 2021 Total})

I want the Sum Total if the Existing/Innovation column says "Existing" and the PO column does NOT say "TBD".

I have also tried this, thinking I have put the range and criteria in the wrong order:

=SUMIFS({2021 YTD Budget : 2021 Total}, {2021 YTD Budget : Existing/Innovation}, "Existing", [{2021 YTD Budget : PO#}, NOT(@cell = "TBD")])

Does this make sense?

Thank you!

Best Answer

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

    Hey

    Your bottom formula is almost there. Remove the square brackets - square brackets are used when you're referencing a column on the same sheet and that column name contains a space, special character or number.

    =SUMIFS({2021 YTD Budget : 2021 Total}, {2021 YTD Budget : Existing/Innovation}, "Existing", {2021 YTD Budget : PO#}, NOT(@cell = "TBD"))

    As you correctly wrote, SUMIFS has the syntax (range to be summed, range1, criteria1, range2, criteria2, range3, criteria3, etc)

    cheers,

Answers

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

    Hey

    Your bottom formula is almost there. Remove the square brackets - square brackets are used when you're referencing a column on the same sheet and that column name contains a space, special character or number.

    =SUMIFS({2021 YTD Budget : 2021 Total}, {2021 YTD Budget : Existing/Innovation}, "Existing", {2021 YTD Budget : PO#}, NOT(@cell = "TBD"))

    As you correctly wrote, SUMIFS has the syntax (range to be summed, range1, criteria1, range2, criteria2, range3, criteria3, etc)

    cheers,

  • Ryan Brock
    Options

    Perfect, thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!