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?
Answers

You will need to create a helper multiselect 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.

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 multiselect 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)

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!