Countif with dropdown multi-select colum

Maggie Lackey

I have a dropdown multi-select column with about 10 dropdown options from which to choose. So obviously I can choose more than one option in each cell! I'm trying to create a chart for my dashboard that shows how often each of the 10 options is chosen. So I've created a countif equation for each of the 10 dropdown options which references the dropdown multi-select column. It's working really well when there is only ONE option in a cell, but if I have more than one (since it's multi-select, I often do!), then the countif equation is not picking it up. Is there a way around that? Thank you!

MCorbin

Use "Contains" in your formula:

=COUNTIF(MultiSelect:MultiSelect, CONTAINS(Option@row, @cell))

Maggie Lackey

Thank you! My formula is a bit different (obviously wrong!) and doesn't work if I put CONTAINS in it.

=COUNTIF({CDHE Consultation Program Range 5}, "SOGI"). This formula populated when I chose COUNTIF from the advanced options in the formula dropdown. I just chose which sheet to reference and put in "SOGI".

CDHE Consultation Program is the Sheet I'm referencing. Range 5 must be the column. "SOGI" is the value I want it to contain. Would you be able to send me the correct formula with COUNTIF & CONTAINS?

Thank you.

Heather Duff

Hi @Maggie Lackey ,

Try this:

=COUNTIF({CDHE Consultation Program Range 5}, CONTAINS("SOGI", @cell))

Best,

Heather

Maggie Lackey

Yes! I did actually figure that out earlier. It's perfect and I'm really excited!

Thanks!

Valerie Lawson

I am having a similiar problem and the CONTAINS function did not fix the problem.

I am trying to get a count of the number of tasks assigned to myself "Valerie" within a multi-select drop down that are not done (aka false in checkbox column).

Here is what I have so far. I have tried multiple different formula variations and cannot get it to work. I have a filter which works perfectly, so I know the count I am looking for, but it keeps returning a count of 0.

Screen Shot 2021-04-30 at 9.14.29 AM.png

Heather Duff

Hi Valerie,

This is a shot in the dark, but try this:

=COUNTIFS([action item assigned to:]:[action item assigned to:], CONTAINS("Valerie", @cell), [action item done:]:[action item done:],0)

Let me know if it works!

Best,

Heather