Stumped on formula for Status of parent task with multiple children and criteria

Amy W

Hi All,

I'm trying to write a formula for which the parent task status is based on the following logic with regards to the status of the children:

• If all child tasks are “not started”, the parent task should be “not started”
• If all child tasks are “completed”, the parent task should be “completed”
• Any other combination would be "in progress" (ie if some or all child tasks are "in progress", some are completed some are not started)

Thanks in advance for your help!

Mike TV

@Amy W

Put this into the Status column of each Parent row:

=IF(COUNTIFS(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress")))

Mike TV

@Amy W

Put this into the Status column of each Parent row:

=IF(COUNTIFS(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress")))

Amy W

@Mike TV Thank you so much! This worked! Greatly appreciated.