SUMIFS excluding criteria

I am trying to sum the amount awarded to programs that have been won by all clients except Client X, Client Y or Client Z but my formula below is coming up #UNPARSEABLE. What am I doing wrong?

=SUMIFS([Amount Awarded]:[Amount Awarded], [Customer Name]:[Customer Name], <> ”Client

X", <> ”Client Y”,  <> “Client Z”, Status:Status, "5_Closed Won")

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @akarlsson,

    You need to include an AND for the criteria or to to have the Customer Name column referenced for each client:

    =SUMIFS([Amount Awarded]:[Amount Awarded], [Customer Name]:[Customer Name], AND(@cell <> "Client X", @cell <> "Client Y", @cell <> "Client Z"), Status:Status, "5_Closed Won")

    OR

    =SUMIFS([Amount Awarded]:[Amount Awarded], [Customer Name]:[Customer Name], <>"Client X", [Customer Name]:[Customer Name], <>"Client Y", [Customer Name]:[Customer Name], <>"Client Z", Status:Status, "5_Closed Won")

    Hope this helps, but if you've any problems/questions then just let us know!

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @akarlsson,

    You need to include an AND for the criteria or to to have the Customer Name column referenced for each client:

    =SUMIFS([Amount Awarded]:[Amount Awarded], [Customer Name]:[Customer Name], AND(@cell <> "Client X", @cell <> "Client Y", @cell <> "Client Z"), Status:Status, "5_Closed Won")

    OR

    =SUMIFS([Amount Awarded]:[Amount Awarded], [Customer Name]:[Customer Name], <>"Client X", [Customer Name]:[Customer Name], <>"Client Y", [Customer Name]:[Customer Name], <>"Client Z", Status:Status, "5_Closed Won")

    Hope this helps, but if you've any problems/questions then just let us know!

  • akarlsson
    akarlsson ✭✭✭

    Both work! Thanks, Nick.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!