I want a formula that specifies the lowest child of an ancestor, parent, child hierarchy.

lhebert

I am using the Hierarchy formula, =COUNT(ANCESTORS()), to label my project tasks. A lot of the child tasks have tasks below that, going to a hierarchy of 5. I want to create metrics sheet that shows the estimated completion date for the lowest level child. Because the levels vary by task, I cannot choose a specific hierarchy number to create that metric sheet off of.

The goal is to only count the tasks, not the parent lines.

Ryan Sides

Hi there! you can use the Descendants formula for that. Just add another column for it.

=COUNT(DESCENDANTS([Hier Level]@row))

Then include when descendants column = 0 in your COUNTIFS formula.