Count Distinct Using a Text Column with Values Separated by Commas

Hello! I have a column with a list of values separated by commas. I am looking to count the number of values separated by commas and eliminate duplicates.

I have tried creating a helper column that converts the values to a multiselect, but I can't figure out the =COUNT(DISTINCT formula from there.

Here is a screenshot example. In this example, I would like to return a value of 5. I hope this makes sense!

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

In a separate sheet, use a JOIN function in a multi-select dropdown field to join the entire multi-select field from the source sheet. Make sure to use CHAR(10) as your delimiter.

A multi-select dropdown will automatically filter out duplicates at which point you can use a COUNTM function to count how many entries are in the JOIN cell.

=JOIN({Source Sheet Multi-Select Column}, CHAR(10))

=COUNTM([Multi-Select Column Name]@row)

Bethany Norman

Paul, thank you! Is there any way to do it in the same sheet or I have to use a separate sheet?

Paul Newcome

You can do it in the same sheet. Just use the same formulas above but replace the {Cross Sheet Reference} with a reference to Helper:Helper.

Bethany Norman

It worked!! 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.