Formula to count individual option in multi select list within sheet summary

Options
Marli
Marli ✭✭
edited 08/15/23 in Formulas and Functions

My sheet has a column called "Skills" which has 7 multi select options. I would like to include a sheet summary that counts how many times each option is selected.

In this screenshot the formula =COUNTM(Skills:Skills, "Policy writing") counts everything in the Skills column, not just "policy writing".

In the formula =COUNTIF(Skills:Skills, "Policy writing"), the sheet summary dosnt count "policy writing" where there is another skill in the cell for example, "project management".

Could anyone help with constructing the formula so that it counts all criterion?

My thanks in advance!


Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Marli,

    For multi-select dropdowns you would need to use the HAS function within your COUNTIF. For example for finding all cells containing "Policy Writing" (whether alone or with other options):

    =COUNTIF(Skills:Skills, HAS(@cell, "Policy Writing"))

    Hope this helps, but if you've any questions or comments then just post! 😊

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Marli,

    For multi-select dropdowns you would need to use the HAS function within your COUNTIF. For example for finding all cells containing "Policy Writing" (whether alone or with other options):

    =COUNTIF(Skills:Skills, HAS(@cell, "Policy Writing"))

    Hope this helps, but if you've any questions or comments then just post! 😊

  • Marli
    Marli ✭✭
    Options

    Thanks so much Nick! That fixed it. Greatly appreciated

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!