Count+Distinct

Javier_Cubix

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

Genevieve P.

Hi @Javier_Cubix

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

Genevieve P.

Hi @Javier_Cubix

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

Javier_Cubix

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

Genevieve P.

Hi @Javier_Cubix

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)