COUNTM not working

Hello community, I have a formula to count distinct values in my column. However, it is not working how I'd like it to. This is the formula with the image below that it relates to:

=COUNTM(DISTINCT([19th Mar]3:[19th Mar]60))

The number of counts is 5, but it should be 3 because 'FG' 'BK' are repeated.

I assume it's not working because of multiple cell values in one cell.

Does anyone have a solution?

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

You will need to create a helper multi-select dropdown type column and use a JOIN function with a CHAR(10) delimiter (line break) then do just a basic COUNTM on the cell containing the JOIN.

Sam Swain

https://community.smartsheet.com/discussion/comment/368314#Comment_368314

Hi Paul! I'm not sure I understand. I did that but it returned the number 5 again. Maybe I didn't understand. This is what I did:

1) Create a multi-select dropdown column (with no values to choose from)

2) Create a formula in the new helper column that is: =JOIN(CHAR(10))

3) In my original column I enter the formula =COUNTM([19th Mar]3:[19th Mar]60)

Paul Newcome

The formula in the helper column only goes in one cell.

=JOIN([Column Name]:[Column Name], CHAR(10))

Then you use the COUNTM on the cell containing the JOIN formula.

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.