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.

ocak

@Paul Newcome Can you please explain this more? Is what I have added below what you're suggesting?

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

When I used this formula, it does mark a section that has some tasks as "Not Applicable" as "Not Started", but all other tasks were automatically changed to "Complete" status.

Any help is much appreciated, thank you!!

Paul Newcome

Can you provide some screenshots of the data that is not working as expected?

ocak

Yes, the status data column is below. The grey rows are tasks that have been marked "Not Applicable". Thank you for your help!

Paul Newcome

Can you provide those same screenshots with the primary column so I can see hierarchy?

ocak

Paul Newcome

Based on this screenshot, it looks like it is working. Everything is marked as "Not Started" and so are the parent rows.

ocak

This is with the formula applied. It removes the status dropdown options altogether. Some rows were already marked as "In Progress" or "Not Applicable" and were changed to "Complete"

Paul Newcome

Yes. Once you apply a formula to a cell, it will overwrite any data that was previously in it. It looks like your formula is designed to be a rollup type of output for child rows, so it should only be applied to parent rows.

ocak

Thank you so much for your help!!!

Paul Newcome

Happy to help. 👍️