Formula help please! What formula to mark the parent row "Complete" with an "Not Applicable" task?

PMChristine

In my status column I am using this formula auto update my my parent row:

=IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Applicable") > 0, "In Progress")))))

It works great for all options but when I have a task >0 that is "Not Applicable" and everything else is "Complete" I need the parent row to state "Complete"

I tried:

=IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Not Applicable", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Applicable") > 0, "In Progress")))))

But the status comes out "In Progress"

I tried:

=IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Applicable") = COUNT(CHILDREN()), " Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Applicable") > 0, "In Progress")))))

But the status comes out "In Progress"

What do I need to do if all cells are all "Complete" with one or more are "Not Applicable" and I want the parent row to say "Complete"?

Thank you in advance!!