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

Iris Riddell

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,

Iris

Paul Newcome

Try something like this...

=COUNTIFS({Criteria A}, CONTAINS("Mental Health Concerns", @cell))

Paul Newcome

Try something like this...

=COUNTIFS({Criteria A}, CONTAINS("Mental Health Concerns", @cell))

Iris Riddell

That did it! Thank you so much! I did try something with CONTAINS at some point but must not have done it quite right... That's a real relief. :)

Paul Newcome

Happy to help! 👍️

Brian Gray

I'm new to Smartsheet also and I'm trying to do something very similar but I don't understand the @cell reference. Which cells is the @cell referencing?

Andrée Starå

Hi @Brian Gray

The @cell function looks at each cell in the range.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Brian Gray

Thank you! That was a big help! It works now.

Andrée Starå

@Brian Gray

You're more than welcome!