SUMIFS excluding criteria

akarlsson

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")

Nick Korna

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!

Nick Korna

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

Both work! Thanks, Nick.