Parent/Child Status Update - Status Roll-Up

bking8

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

Kelly Moore

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

Kelly Moore

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

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

Happy this worked for you.

Kelly