Help!! CountM but Distinct

mhawkins

Hello All,

Our org uses multi-select 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!

Matt Lynn-PCG

@mhawkins

1.) You need to make a column (such as your "Total # of Unique options selected ever?" column to be a multi-select drop down. In that column/cell put this formula:

=JOIN(COLLECT(Options:Options, Options:Options, @cell <> ""), CHAR(10))

By doing this the multi-select 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.

Matt Lynn-PCG

@mhawkins

1.) You need to make a column (such as your "Total # of Unique options selected ever?" column to be a multi-select drop down. In that column/cell put this formula:

=JOIN(COLLECT(Options:Options, Options:Options, @cell <> ""), CHAR(10))

By doing this the multi-select 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.

mhawkins

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!

Matt Lynn-PCG

@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.