How can i calculate the % Complete at a sub-parent level based on multiple criteria?

Hello and thanks in advance for any assistance. I have spent a ton of time attempting to solve and am tossing up the flag. I have a sheet that needs minimal column formulas due to the line/cell count potentially hitting the base reference limits.

I have an overall % complete being calculated on the sheet itself already. now i need to have individual ones for each of the sub-parents highlighted as "TSYS" & "Fiserv".

It needs to be based on "NA" being 0 and the "Status" being either "P" or "C". So essentially an average of the children based on that criteria. However these sub-parent titles are not unique as this is a baseline that gets copied out multiple times. looking to imbed the formula on those lines so when copied it goes along with it.

Find more posts tagged with

Sheets

Formulas

Automations

Project Management

Accepted answers

All comments

Devin Lee

I think what you are asking for is to give the percentage of tasks under a parent that are either P or C. This can be accomplished using the Children formula.

=COUNTIF(CHILDREN(Status@row), OR(@cell = "P", @cell = "C")) / COUNT(CHILDREN(Status@row))

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.