How do I create a countifs formula if I have a column with multi drop down?

Options

How do I create a countifs formula if I have a column with multi drop down and I need to specify which word to include for my count count? I would like to use the HAS function but it is not working as I am also referencing the data from another sheet so I can not use @cell with the HAS.

Best Answer

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    I think you can use CONTAINS to do that.

  • Joy Kwan
    Options

    It is coming back with Zero. Would you be able to show how I would write the formula?

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    Sure thing. In this formula I have a column called "multi" that I have a multiple drop-down selector with 1,2,3,4,5 available to pick. I populated the first cell with 1,2,3 and the second with 2, and the rest with other numbers that aren't 2. I'm looking for 2 and I expect it to return 2 instances since it is in the first mutli-select and the second.

    =COUNTIF(multi:multi, CONTAINS("2", @cell))

    So this looks at the whole range of the column (multi:multi) and checks each cell (@cell) on whether or not it contains a 2.

    Would that work for what you're looking for?

  • Joy Kwan
    Options

    I would like to use the COUNTIFS as I have multiple criteria. The formula works for 1 criteria but when I add another criteria it returns a Zero. Thank you!

  • Joy Kwan
    Options

    Thanks! It works!!

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    Awesome! Glad you got it going.

  • Sharon I
    Options

    Hi David, I have spent an age on this exact same problem and your answer has solved it for me! Thank you, you are amazing!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!