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

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.

Find more posts tagged with

Formulas

functionality

Automations

Project Management

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.