Parent/Child Status Update - Status Roll-Up

I currently have the following formula that has been working - but I'm trying to update it with our appropriate statuses but getting unparseable error

Current Formula - =IF(COUNTIFS("On Hold", CHILDREN()), "On hold", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "In Progress")))

We have removed "On Hold" and updated our statuses to the following: Not Started, In Progress, Complete, At Risk, No Longer Pursue, I am hoping to have the Parent Status update based on the following statements

If all children lines are Not Started, Parent =Not Started

If all children lines are In Progress, Parent =In Progress

If all children lines are Complete, Parent =Complete

If all children lines are At Risk, Parent = At Risk

If all children lines are No Longer Pursue, Parent = No Longer Pursue

If multiple statuses of children lines, Parent = In Progress

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @bking8

    =IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), "At Risk") = COUNT(CHILDREN()), "At Risk", IF(COUNTIFS(CHILDREN(), "No Longer Pursue") = COUNT(CHILDREN()), "No Longer Pursue", IF(COUNTIFS(CHILDREN(),"In Progress")=COUNT(CHILDREN()), "In Progress", "In Progress")))))

    Does this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @bking8

    =IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), "At Risk") = COUNT(CHILDREN()), "At Risk", IF(COUNTIFS(CHILDREN(), "No Longer Pursue") = COUNT(CHILDREN()), "No Longer Pursue", IF(COUNTIFS(CHILDREN(),"In Progress")=COUNT(CHILDREN()), "In Progress", "In Progress")))))

    Does this work for you?

    Kelly

  • bking8
    bking8 ✭✭✭

    Hello @Kelly Moore - this worked beautifully! Thank you! I wish I could have sorted it out but that last part where it becomes the combined options always trips me up!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Happy this worked for you.

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!