# COUNTIF for Metric Sheet with Multiple Cell Values

Options
✭✭

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

• ✭✭✭✭✭✭
Options

Hi @Leslie E,

=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

• ✭✭
Options

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!