Help with Parent Line Formula

Stacib83

I need a formula for my parent line with all possibilities of child line combinations

My current statuses are:

Not Started

Completed

In Progress On Track

In Progress Potential Risk

Overdue

I got this part done;

If all child lines are completed return Completed

If all child lines are Not Started return Not Started

If all child lines are In Progress On Track return In Progress On Track

If all child lines are In Progress Potential Risk return In Progress Potential Risk

If any child lines are Overdue return Overdue

This is where I get hung up .

If there are a mix of Not Started and Completed return In Progress On Track

If there are a mix of Not Started and In Progress Potential Risk return In Progress Potential Risk

If there are a mix of Not Started and In Progress On Track return In Progress On Track

If there are a mix of Completed and In Progress On Track return In Progress On Track

If there are a mix of Completed and In Progress Potential Risk return In Progress Potential Risk

Melissa Yamada

Hello @Stacib83

Try this:

=IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIF(CHILDREN(), "In Progress On Track") = COUNT(CHILDREN()), "In Progress On Track", IF(COUNTIF(CHILDREN(), "In Progress Potential Risk") = COUNT(CHILDREN()), "In Progress Potential Risk", IF(HAS(CHILDREN(), "Overdue"), "Overdue", IF(HAS(CHILDREN(), "In Progress Potential Risk"), "In Progress Potential Risk", IF(OR(HAS(CHILDREN(), "Not Started"), HAS(CHILDREN(), "Completed"), HAS(CHILDREN(), "In Progress On Track")), "In Progress On Track")))))))

Paul Newcome

This should work for you:

=IF(COUNTIFS(CHILDREN(), @cell = "Overdue") > 0, "Overdue", IF(COUNT(DISTINCT(CHILDREN())) = 1, INDEX(CHILDREN(), 1), IF(COUNTIFS(CHILDREN(), @cell = "In Progress Potential Risk") > 0, "In Progress Potential Risk", "In Progress On Track")))

Stacib83

Thank you so much this worked perfectly for me

Paul Newcome

Happy to help. 👍️