Writing a formula to count criteria in a multi-select column

Hi everyone!

Newcomer to Smartsheet here. I work for a small family wellbeing agency and I'm trying to write a formula to tally up the criteria provided for why someone is applying/being referred for one of our programmes.

We have "Section A criteria" and "Section B criteria" which are chosen from multi-select sections on a form. These criteria are things like "Mental health concerns", "Low income", "Left school early" and so on.

I'm also trying to write these formulas in a separate sheet, in which I've managed to get a single-selection count formula working, but this multi-select business is tripping me up.

A couple of formulas (and variants thereof) I've tried are:

=COUNTIF({CriteriaA}, "Mental health concerns") (Which is returning 0)

=COUNTIF({CriteriaA}, FIND("Mental health concerns", @row, @cell)) (Which is returning #UNPARSEABLE)

...Where CriteriaA is the selected range for "Section A criteria" on the original sheet.

I've been bashing my head against this for a little while and feel like I've starting going round in circles, and would appreciate some guidance!

Thanks in advance,


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!