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

Marli

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!

Skills.JPG

Nick Korna

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! 😊

Nick Korna

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

Thanks so much Nick! That fixed it. Greatly appreciated