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 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.
-
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)
-
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!