Help!! CountM but Distinct
Hello All,
Our org uses multiselect columns a lot, and would like to know the total # of unique options ever selected within a range. In the example below, this would mean out of those 4 rows, I would like a formula that returns "3", meaning that 3 unique options were selected, rather than 5 total options as the =countm formula helps with. In theory, using the distinct formula within countm. We have over 500 "Options", so keeping that it mind when needing to set anything else up manually.
I have called Smartsheet Support but they weren't able to help craft any formulas or solutions. Been working on this for months it feels (I'm not an expert and it's a peripheral job for me. Can anyone help? Thank you!
Best Answer

1.) You need to make a column (such as your "Total # of Unique options selected ever?" column to be a multiselect drop down. In that column/cell put this formula:
=JOIN(COLLECT(Options:Options, Options:Options, @cell <> ""), CHAR(10))
By doing this the multiselect formula will only show each discovered option 1 time, if it's there.
2.) THEN in another cell put the =countm([ref above formula/cell]) formula and you'll get 3.
Answers

1.) You need to make a column (such as your "Total # of Unique options selected ever?" column to be a multiselect drop down. In that column/cell put this formula:
=JOIN(COLLECT(Options:Options, Options:Options, @cell <> ""), CHAR(10))
By doing this the multiselect formula will only show each discovered option 1 time, if it's there.
2.) THEN in another cell put the =countm([ref above formula/cell]) formula and you'll get 3.

Thank you!! That worked great to an extent, but I ran into another problem. The Join column ends up being over the character limit. (My "options" are really long, which is necessary  it helps staff quickly type up portions of very long names while in the entry form). So I can't get an accurate # for what I need. Is there any way to extend the character limit of a call, or enter a formula in a different place where there are no limits, so that I can get a full list of unique Options and then count?
Thank you!

@mhawkins You cannot extend the character limit. Off the top of my head without digging deeper with the actual data the only idea I have would be to figure out a way to shorten/abbreviate/concatenate the data into smaller unique values… but that's just a rough idea.
Could be possible to break it up somehow into categories. Example have 26 cells, join only the ones that start with A, then B etc…. then count and total those. Or separate by dates, or locations or some other values to simply segregate the data in to smaller size sets, then just total it all. It would work the same if you're data has some additional context or pattern. I couldn't help more without seeing the actual data.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!