COUNTIF for Metric Sheet with Multiple Cell Values


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


  • 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!