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"))
Best Answer

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).
Answers

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! 😊

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?

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).
Help Article Resources
Categories
Check out the Formula Handbook template!