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

=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)
Help Article Resources
Categories
Check out the Formula Handbook template!