Formula to evaluate child statuses to determine the parent status

Elle Doty

I have different milestones in a project and want the parent milestone level to show a general "temp" based on the statuses in the child tasks below it. The thought is, if any of the child tasks contain a status of "delayed, on hold, blocked, requires update" the status cell on the parent Milestone row should turn yellow.

Is this possible?

I'm currently using this:

=IF(COUNTIFS(STATUS64:STATUS71, "Complete") = COUNT(STATUS64:STATUS71), "COMPLETE", IF(COUNTIFS(STATUS64:STATUS71, "Not Started") = COUNT(STATUS64:STATUS71), "Not Started", "In Progress"))

This shows when a whole section is complete.

Or, maybe a better way to ask: What's a better way to gauge the subtasks at a glance to see visually see potential project risk?

Bassam Khalil

Hi @Elle Doty 

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Andrée Starå

Hi @Elle Doty

I hope you're well and safe!

We'd probably use the CONTAINS function to structure the formula.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!