Count how many times a multi-select value was selected in a cell range or column

Options

Hi,

I am trying to count how many projects from another sheet has a specific OKR value selected in a multi-select dropdown column.

Example: There are 4 projects, all of which could be assigned to a single or multiple OKR values.

I need a formula to count the number of times, e.g. 'Improve Operations Efficiency', was selected and in this case, it should be 3 times but the COUNTIF formula is only pulling a total of 2.


Please help. 🙂

Best Answer

  • Lidiya Shutaya
    Lidiya Shutaya ✭✭✭
    Answer ✓
    Options

    Hello Zahir,

    Please try the following:

    =COUNTIF([Multi-Select Column]:[Multi-Select Column], CONTAINS("Improve Operations Efficiency", @cell))

    The Contains function and similar functions usually like to look at one cell at a time and will not work as well with ranges.

    Hope this helps!

    Lidiya Shutaya

Answers

  • Lidiya Shutaya
    Lidiya Shutaya ✭✭✭
    Answer ✓
    Options

    Hello Zahir,

    Please try the following:

    =COUNTIF([Multi-Select Column]:[Multi-Select Column], CONTAINS("Improve Operations Efficiency", @cell))

    The Contains function and similar functions usually like to look at one cell at a time and will not work as well with ranges.

    Hope this helps!

    Lidiya Shutaya

  • Zahir Cassam
    Zahir Cassam ✭✭
    Options

    Hi Lidiya,

    Thank you. This works great! 😀

    Also, I took it a bit further so instead of having to type out each OKR value in the formula, I included a vlookup function within it so it references the label text to the left.

    Now I can copy the this formula over without having to customize it per OKR.

    Thanks for the help!

    Zahir

  • Lidiya Shutaya
    Lidiya Shutaya ✭✭✭
    Options

    Zahir,

    Glad to hear it worked! Awesome that you took it a step further to reduce headaches later down the line!

    Lidiya

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!