Parent Status Roll up from Child/Decendants

KaseyK

Ive tried so many formulas and cant get this right!

If at least 1 child = "Issue" then Issue for Parent

If at least 1 child = "In Progress" then In Progress for Parent

If ALL children = "Completed" then Completed for Parent

Since this is a dropdown column Im hard coding and locking the parent lines.

=IF(COUNT(CHILDREN([Task Status]305)) > 0, IF(COUNTIF(CHILDREN([Task Status]305)), "In Progress") = COUNT(CHILDREN([Task Status]305))), "In Progress", IF(COUNTIF(CHILDREN([Task Status]305)), "Completed") = COUNT(CHILDREN([Task Status]305))), "Completed", IF(COUNTIF(CHILDREN([Task Status]305)), "Issue") = COUNT(CHILDREN([Task Status]305))), "Issue", "n/a"))))), IF([Task Status]@row) = "In Progress", "In Progress", IF([Task Status]@row) = "Complete", "Complete", IF([Task Status]@row) = "Issue", "Issue", "")))))

Samuel Mueller

@KaseyK Good morning, try this formula..

=IF(CONTAINS("issue", CHILDREN()), "Issue", IF(CONTAINS("In Progress", CHILDREN()), "In Progress", "Complete"))

KaseyK

Thanks Sam! That was defaulting blanks to Complete but Im closer with the formula below. I also have descendants to subbing that with children seems to do the trick. How can I only mark complete if ALL descendants = Completed?

=IF(CONTAINS("Issue", DESCENDANTS()), "Issue", IF(CONTAINS("In Progress", DESCENDANTS()), "In Progress", IF(CONTAINS("Complete", DESCENDANTS()), "Completed", "Not Started")))

Samuel Mueller

@KaseyK Soo, if you have 1 blank, and three complete, what would you want the status to say? In Progress?

IF so try this

=IF(CONTAINS("Issue", DESCENDANTS()), "Issue", IF(CONTAINS("In Progress", DESCENDANTS()), "In Progress", IF(COUNT(CHILDREN()) <> COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ""), "In Progress", IF(CONTAINS("Complete", DESCENDANTS()), "Completed", "Not Started"))))

KaseyK

Yes, so In Progress and Issue being the critical status' here, and Completed if none of the status are blank, In Progress or Issue. The default is "Not Started" so if status is blank on all lines, thats the status on each parent line.

Samuel Mueller

@KaseyK So with the formula I just added above, in order

if any status says issues, it will say issue, if any status says in progress it will say in progress.

If some of the descendants are blank, and all the others say complete, it says "In Progress"

If everything is complete, it will say complete.

in this scenario, what should be returned, "In Progress"? if not you can replace that "In Progress" with anything. : "If some of the descendants are blank, and all the others say complete, it says "In Progress""

Updated to account for all blank and not started..

=IF(CONTAINS("Issue", DESCENDANTS()), "Issue", IF(CONTAINS("In Progress", DESCENDANTS()), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") = 0, CONTAINS("Complete", DESCENDANTS())), "Completed", IF(AND(COUNTIF(CHILDREN(), "") > 0, CONTAINS("Complete", DESCENDANTS())), "In Progress", "Not Started"))))

KaseyK

Awesome!! This is so very close but I just realized my sheet will filter based on the scenario, so if child tasks are filtered out of the view, the parent will never mark complete. Any way to ignore blanks then "Complete"?

Samuel Mueller

@KaseyK so if you have 5 lines, 3 are complete, and two are blank, you want it to say complete?

Or what is the criteria for ignore?