Count+Distinct

Javier_Cubix
Javier_Cubix ✭
in Formulas and Functions
image.png

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

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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.

    Screen Shot 2021-10-14 at 4.54.00 PM.png


    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

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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.

    Screen Shot 2021-10-14 at 4.54.00 PM.png


    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

    · Share on FacebookShare on Twitter
  • Javier_Cubix
    Javier_Cubix ✭
    edited 10/20/21
    image.png

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

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    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)

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!