SUMIFS and Exclusion formula needed

Ryan Brock

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

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,

Kelly Moore

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

Perfect, thanks!