Count+Distinct
=COUNT(DISTINCT(CHILDREN()))
I using the formula above to count the distinct number of bays for different shipments, some shipments would have all of the bays in one cell. Is there a way to modify this formula to count the bays within one cell separately? (for clarification the example above would be 3 distinct bays while it is only counting 2)
Best Answer

Since your column is a primary Text/Number Column, the Distinct function is searching for a distinct cell value as a whole, without being able to identify that a space means there's a second value in the same cell. It sees "C01 C02 C03" as one full string. Does that make sense?
We can parse these values out by turning them into individual choices in a multiselect column. The way I would do this is add a helper column next to your current Primary column. Then use this formula:
=SUBSTITUTE([Primary Column]@row, " ", CHAR(10))
This will replace every space (or "") in your cell with a line break, turning them into separate values. You can make this into a column formula.
Once you have a multiselect column to evaluate, adjust your current CHILDREN function to look at this helper column instead of your primary column:
=COUNT(DISTINCT(CHILDREN([Multi Select]@row)))
Then you can hide this Multi Select column, if you'd like. Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

Since your column is a primary Text/Number Column, the Distinct function is searching for a distinct cell value as a whole, without being able to identify that a space means there's a second value in the same cell. It sees "C01 C02 C03" as one full string. Does that make sense?
We can parse these values out by turning them into individual choices in a multiselect column. The way I would do this is add a helper column next to your current Primary column. Then use this formula:
=SUBSTITUTE([Primary Column]@row, " ", CHAR(10))
This will replace every space (or "") in your cell with a line break, turning them into separate values. You can make this into a column formula.
Once you have a multiselect column to evaluate, adjust your current CHILDREN function to look at this helper column instead of your primary column:
=COUNT(DISTINCT(CHILDREN([Multi Select]@row)))
Then you can hide this Multi Select column, if you'd like. Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
=COUNT(DISTINCT(CHILDREN(BAYS@row)))
=SUBSTITUTE(LOCATION@row, " ", CHAR(10))
I got the formulas to work but for some reason even though the locations are distinct, it is not picking them up as separate locations.

My apologies! I missed two steps here.
If you're using a Parent row, then in the top row of the multiselect, put this formula:
=JOIN(CHILDREN(), CHAR(10))
This will bring together all the unique values selected in the children cells. Then you can use the COUNTM function for just that one cell, like so:
=COUNTM([Multi Select]@row)
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 209 Industry Talk
 441 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 297 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!