Parent Status Roll up from Child/Decendants

Options

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", "")))))



Answers

  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    @KaseyK Good morning, try this formula..

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

  • KaseyK
    KaseyK ✭✭
    edited 05/11/23
    Options

    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
    Samuel Mueller Overachievers
    edited 05/11/23
    Options

    @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
    KaseyK ✭✭
    Options

    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
    Samuel Mueller Overachievers
    edited 05/11/23
    Options

    @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
    KaseyK ✭✭
    Options

    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
    Samuel Mueller Overachievers
    Options

    @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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!