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

Paul Newcome

Try this:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "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")))))

Paul Newcome

Try this:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "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")))))

PMChristine

THANK YOU!!!

THANK YOU!!!

THANK YOU!!!

Paul Newcome

Happy to help. 👍️

PMChristine

Ran into another issue wondering if you could help again, please :)

Using the suggested formula when I have a section that has some tasks as "Not Applicable" it changes to "In Progress" when all other tasks are "Not Started"

I would like it to say "Not Started" I changed the formula to this:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "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"))))

Removing the: IF(COUNTIF(CHILDREN(), "Not Applicable") > 0, "In Progress" in hopes it would change to "Not Started" but it didn't.

Any suggestions?

Thank you in advance!!

Paul Newcome

You would use the same OR criteria from the first COUNTIFS in any other COUNTIFS that also needs to include the "Not Applicable" status.