SUMIFS and Exclusion formula needed

Question

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!

Kelly Moore · Accepted Answer

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,