If Formula for Complete, In Progress, At Risk, NA

Hi,

I need help with a formula so that the Child can filter to complete, in progress or at risk based on the status for the items of the descendants. Additionally, when NA is set as a status for a line item I don't want NA to be set as the status for the child, since that is not an accurate representation. I think I have the formula started below I just don't know how to add on the 2 Not Applicable's statements I need.

=IF(COUNTIFS(CHILDREN(), "at risk") = COUNT(CHILDREN()), "at risk", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "At risk"))

Find more posts tagged with

Formulas

Accepted answers

Nick Korna

Of course - switch round the middle part of the formula slightly:

=IF(COUNTIF(CHILDREN(), "Complete") = (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "Not Applicable")), "Complete", IF(COUNTIF(CHILDREN(), "At Risk") >= 1, "At Risk", "In Progress"))

Now if any of the child tasks are "At Risk", then the parent will display this.

You can also use the above formula as the basis for your ancestor rows - it will look at the level below and apply the same logic (i.e. complete if all complete, at risk if any are, otherwise in progress).

All comments

Nick Korna

Hi @abbey123,

Your formula seems to be suggesting that the parent will be:

"Complete" if all children are either complete or NA

"At Risk" if all children are either complete or NA

Otherwise: In Progress

If these assumptions are correct, then this formula should work:

=IF(COUNTIF(CHILDREN(), "Complete") = (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "Not Applicable")), "Complete", IF(COUNTIF(CHILDREN(), "At Risk") = (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "Not Applicable")), "At Risk", "In Progress"))

Example with the formula in the parent Status cells:

If I've misunderstood something or you want some additional refining done, just post the details.

If not, hope this post has helped! 😊

abbey123

Thank you Nick, this definitely did help. The Parent is the "CSU", The Children is the "CSU Disconnect" and the Descendants is "is there a csu to be moved". This formula worked for the most part part however, If a task is marked as at risk the the formula shows up as in progress and not as at risk, is there a way to add at risk as a status?

Nick Korna

Of course - switch round the middle part of the formula slightly:

=IF(COUNTIF(CHILDREN(), "Complete") = (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "Not Applicable")), "Complete", IF(COUNTIF(CHILDREN(), "At Risk") >= 1, "At Risk", "In Progress"))

Now if any of the child tasks are "At Risk", then the parent will display this.

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.