Child task status rollup to Parent task

RosieO · 09/17/24

Hi there,

I am trying to do a formula that says something along the lines of the below:

If all tasks are marked as complete, update parent to Complete
If any task is marked as 'in progress' update parent to In Progress
If no tasks are started, update parent to Not Started
If majority of tasks are marked as Potential Risk OR Risk / Delay update parent to At Risk
If all tasks are NA / canceled, update parent to Canceled

My status options are:

	Not Started


	In Progress

	Potential Risk

	Risk / Delay

	Complete

	NA /Canceled

I tried looking at other community posts, and found this, but I'm not sure how to incorporate my additional statuses.

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

Please can someone advise? Appreciate any help!

Thanks!

Rosie

Leela Lodhi · 09/18/24

Hi RosieO,

To incorporate your additional statuses and meet your specific conditions, you can try below formula:

=IF(COUNTIFS(CHILDREN(), "NA /Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Potential Risk") + COUNTIFS(CHILDREN(), "Risk / Delay") > COUNT(CHILDREN()) / 2, "At Risk", "")))))

I hope this helps!

Thanks!

Leela Lodhi

RosieO · 09/18/24

Hi Leela, thanks so much for this!

I have one tiny thing thats not working, which is that if the child task is blank, the formula displays as 'Canceled' and I am not sure how to fix to say if the status is blank, display 'Not Started' OR display nothing. Just don't think that Canceled is accurate for this. See screengrab below.

Thank you so much for any additional guidance!!!

NicoLHC · 09/18/24

@RosieO i suggest you to add one or two helper coloum to get a better overview where you are . =COUNT(ANCESTORS([Activity / Item]@row)) same for children.

Try this updated formula considering the blank. =IF(COUNTIFS(CHILDREN(), "NA /Canceled") = COUNT(CHILDREN()), "Canceled",
IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete",
IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress",
IF(COUNTIFS(CHILDREN(), "Not Started") + COUNTIFS(CHILDREN(), "") = COUNT(CHILDREN()), "Not Started",
IF(COUNTIFS(CHILDREN(), "Potential Risk") + COUNTIFS(CHILDREN(), "Risk / Delay") > COUNT(CHILDREN()) / 2, "At Risk", "")))))

RosieO · 09/18/24

Thanks Nico for coming back so quickly. I already have about a million columns in my sheet so was trying to avoid adding anything else if I can.

Apologies for this, but I'm not sure what the difference is between the original formula Leela provided and the one you have provided above?

Thanks!

NicoLHC · 09/18/24

@RosieO my formula accounts for both “Not Started” and blank child tasks by using the following condition:. i hope its alwasy bit more difficult to draft a formula if you not working in the actual sheet.