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,

Iris

Find more posts tagged with

Accepted answers

Paul Newcome

Try something like this...

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

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.