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 multi-select 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 multi-select 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 multi-select 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 multi-select 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 multi-select, 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!