Counting number of different children in a section.

SteveH

 

I would like to count different children only once in a smartsheet section.

 

For example:

 

Parent row:      number of entities

Row 1         :            "Joe Montana"

Row 2        :             "Dwight Clark"

Row 3        :             "Joe Montana"

Row 4        :             "Jerry Rice"

Row 5        :             "Joe Montana"

 

The number of entities should return 3. 

 

Thanks for your help!!!

Steve

Steve L

Steve,

 

Unfortunately, there is no Distinct function, which would help a lot here.  You could get a little clever with formulas, but you have to provide the distinct list within your formula.  Hidden cells somewhere on the sheet could be referenced in the formula to act as a lookup list, and then your formula would be easy to update by dragging formula reference handles on referenced cells.  Here's an example formula you could try, based on your example data above (the names quoted in the formula could be replaced with static cell references - not cells in the hierachy):

 

=IF(COUNTIF(CHILDREN([Primary Column]1), "Joe Montana") > 0, (COUNTIF(CHILDREN([Primary Column]1), "Joe Montana") / COUNTIF(CHILDREN([Primary Column]1), "Joe Montana")), 0) + IF(COUNTIF(CHILDREN([Primary Column]1), "Dwight Clark") > 0, (COUNTIF(CHILDREN([Primary Column]1), "Dwight Clark") / COUNTIF(CHILDREN([Primary Column]1), "Dwight Clark")), 0) + IF(COUNTIF(CHILDREN([Primary Column]1), "Jerry Rice") > 0, (COUNTIF(CHILDREN([Primary Column]1), "Jerry Rice") / COUNTIF(CHILDREN([Primary Column]1), "Jerry Rice")), 0)