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))
Best Answer
-
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")
Answers
-
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?
-
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.
-
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")
-
That works, thanks!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!