COUNTIF for Metric Sheet with Multiple Cell Values

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

Find more posts tagged with

Accepted answers

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.