COUNTIFS / CONTAINS

crystal.hope

I'm struggling using the countifs function referencing another sheet for a multi-select drop down. For this example, I'm trying to count any cells in the group column in the reference sheet that contain "Instruments" where the status is not closed in the same reference sheet.

I'm using the following countifs to do this but it's returning 0 so there is clearly something wrong with my formula.

=COUNTIFS({Status}, <>"Closed", {Group}, CONTAINS("Instruments", {Group}))

The below works but it doesn't count the multi-select drop down cells, so I thought if I tried using a contains to search throughout all of the cells in that column it might help:

=COUNTIFS({Status}, <>"Closed", {Group}, "Instruments")

Any suggestions?

crystal.hope

Nevermind, I figured it out.

I was missing this @cell

=COUNTIFS({Status}, <>"Closed", {Group}, CONTAINS("Instruments", @cell))

crystal.hope

Nevermind, I figured it out.

I was missing this @cell

=COUNTIFS({Status}, <>"Closed", {Group}, CONTAINS("Instruments", @cell))

Genevieve P.

Hi Crystal,

Glad you sorted it out! Please feel free to post again if you need any other formula creation advice.