Can I create a SUMIF "does not contain" with multiple values?

Dana Folsom

Trying to add up the number of people from all cities but, CityA, CityD, CityF, etc in the same argument. Is there a simple SUMIF formula i could use? Currently I have been using:

=SUMIF([City]1:[City]51, <>"NYC", [# People]1:[# People]51)

but i want Smartsheet to exclude multiple cities.

I've tried a few options but felt the most hopeful with the following.. but they didnt work:

=SUMIF([City]1:[City]51, <>"NYC", <>"LAX", [# People]1:[# People]51)

=SUMIF([City]1:[City]51, <>"NYC", OR [City]1:[City]51, <>"LAX", [# People]1:[# People]51)

=SUMIF([City]1:[City]51, <>"NYC", AND [City]1:[City]51, <>"LAX", [# People]1:[# People]51)

this is likely super simple - any ideas?

Paul Newcome

Try something like this...

=SUMIFS([# People]1:[# People]51, City1:City51, AND(@cell <> "LAX", @cell <> "NYC"))

Dana Folsom

Thanks Andrew.  it got me closer! :)

Paul Newcome

Happy to help! 👍️