COUNTIF for Metric Sheet with Multiple Cell Values

Leslie E

Hello,

We have 4 primary locations but several staff who work remotely and enter their various city or state in a non-restricted drop-down column. I am trying to create a metric sheet for a chart that counts the total staff in each of the 4 primary locations and everyone else into a "Remote" bucket. I figured out a crosssheet formula that works but it is counting an additional ~16 staff and I can't figure out why. Can someone help? Thanks in advance!

=COUNTIFS({Primary Location}, NOT(CONTAINS("El Segundo", @cell)), {Primary Location}, NOT(CONTAINS("Aurora", @cell)), {Primary Location}, NOT(CONTAINS("Colorado Springs", @cell)), {Primary Location}, NOT(CONTAINS("Boulder", @cell)))

DKazatsky2

Hi @Leslie E,

Give this a try instead.

=COUNTIFS({Primary Location}, <>"El Segundo", {Primary Location}, <>"Aurora", {Primary Location}, <>"Colorado Springs", {Primary Location}, <>"Boulder")

I can't really explain why this works, where the other doesn't, maybe someone else knows.

Hope this helps,

Dave

Leslie E

Hi Dave,

This formula does count all the rows with a primary location of El Segundo, Aurora, Colorado Springs, and Boulder but I'm looking to count all the rows that are NOT one of these locations (since these are the 4 drop-downs and the rest are free text containing multiple city/state contents). Thoughts on turning this into an excluding formula? Thanks!