Countif with dropdown multi-select colum

Options

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!

Answers

  • MCorbin
    MCorbin Overachievers Alumni
    Options

    Use "Contains" in your formula:

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



  • Maggie Lackey
    Options

    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
    Heather Duff ✭✭✭✭✭✭
    Options

    Hi @Maggie Lackey ,


    Try this:

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


    Best,

    Heather

  • Maggie Lackey
    Options

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

    Thanks!

  • Valerie Lawson
    Options

    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.


  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!