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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That works, thanks!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!