Counting 3 out of 4 values in multiselect column
Hello,
I was curious if there is a way to count for 3 out 4 values in a multiselect column using a single formula. For example, there are four values in multiselect: 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!
Help Article Resources
Categories
Check out the Formula Handbook template!