Counting 3 out of 4 values in multi-select column

Hello,

I was curious if there is a way to count for 3 out 4 values in a multi-select column using a single formula. For example, there are four values in multi-select: Not started, in progress, blocked, and complete. I want to count if one vendor has any of those values except complete. I am trying to use a formula like this but comes back unparseable.

=COUNTIFS({Work Order Request Tracker Range 1}, CONTAINS("Service Pro", @cell), {Work Order Request Tracker Range 2}, CONTAINS(OR("Not started", @cell),("In progress", @cell),("Blocked", @cell)))

I know I could use a formula like the one below for each value and then calculate the sum of cells, but was curious if there is a more efficient formula. Thanks!

=COUNTIFS({Work Order Request Tracker Range 1}, CONTAINS("Tek Express", @cell), {Work Order Request Tracker Range 2}, CONTAINS("In progress", @cell))

Find more posts tagged with

Accepted answers

Paul Newcome

If there will only ever be one option selected, you don't need the CONTAINS functions. Instead though, it is more efficient to count for not "Complete", and that would look like this:

=COUNTIFS({Range}, @cell <> "Complete")

All comments

Paul Newcome

Is it possible they would have more than one option selected? Could we just count cells that don't contain "Complete" instead of trying to count cells that do contain the other statuses?

RobertHapp

In this case, there would only be one option selected. If there is a formula to count cells that contain everything but "Complete", that would work too.

Paul Newcome

If there will only ever be one option selected, you don't need the CONTAINS functions. Instead though, it is more efficient to count for not "Complete", and that would look like this:

=COUNTIFS({Range}, @cell <> "Complete")

RobertHapp

That works, thanks!

Paul Newcome

Happy to help. 👍️

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.