Hierarchy Formula Help

Jeff_Berman

I am trying to write a COUNTIF formula such that the value of each of the lowest level of a hierarchy will be counted. I tried using a CountAncestor helper column but not all groups have the same structure. IE. In some groups, the lowest level child is level 3, and in other groups, it is level 4 (or 5). How can I count the value of cells from the "most child" level of a grouping, no matter how many children there are? (note, I realize in the screenshot it appears as those the CountAncestor idea would work as all of the lowest levels here are a "3" but if you scroll down, some of 4 or 5.

Thanks for your help!

Jeff

Paul Newcome

I would suggest a helper column (checkbox) that will check the box on all child rows (rows that do not have any children of their own/lowest level regardless of number of ancestors).

=IF(COUNT(CHILDREN(Task@row)) = 0, 1)

Paul Newcome

I would suggest a helper column (checkbox) that will check the box on all child rows (rows that do not have any children of their own/lowest level regardless of number of ancestors).

=IF(COUNT(CHILDREN(Task@row)) = 0, 1)

Jeff_Berman

Thank you, Paul! Great suggestion

Paul Newcome

Happy to help. 👍️