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)

 

SteveH

Unfortunately, I don't know what is in each column. Maybe this is not possible. 

Better example would be a list of countries (rather than football players). My goal is to know how many different countries are represented. I don't want to compare every possible country name but instead, just count it once if it appears. 

If we sorted the list first, then compared and skipped those that were the same as the previous, maybe that would work?