How to count the number of multi-select selections that contain a string?

Hi there,

I'm trying to devise a formula that counts the number of multi-select selections in a cell that contain a certain string. The closest I've come is:

=COUNTM(COLLECT([Multi-select]@row, [Multi-select]@row, CONTAINS("STRING", @cell)))

However, this confirms the string is present in any of the selections and then returns the total number of selections. I've experimented with various options, including adding DISTINCT in a number of places, or switching to COUNT(DISTINCT(COLLECT())), but the result (when the string is present) has either been a 1 or the total number of selections.

To clarify, if there are 5 selections and 2 of them have the string in, I want to return a 2.

Thanks in advance!

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

=(LEN([Multi-Select Column]@row) - LEN(SUBSTITUTE([Multi-Select Column]@row, "STRING", ""))) / LEN("STRING")

All comments

Paul Newcome

Try this:

=(LEN([Multi-Select Column]@row) - LEN(SUBSTITUTE([Multi-Select Column]@row, "STRING", ""))) / LEN("STRING")

Philip Robbins

@Paul Newcome fair play. That was not an approach I'd considered at all and it worked a treat. Thank you!!

Paul Newcome

Happy to help. 👍️

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.