Parent Status based on Children Statuses - Need help refining this formula

dhall

Hey Smartsheet Community!

I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders vision.

Here's the formula:

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = AND(COUNTIF(CHILDREN(), "Complete") >= 1, OR(COUNTIF(CHILDREN(), "Canceled") > 0, COUNTIF(CHILDREN(), "On Hold") > 0)), "Complete", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "Not Started") >= 0, COUNTIF(CHILDREN(), "Upcoming") >= 0, COUNTIF(CHILDREN(), "On Hold") >= 0)), "In Progress", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Upcoming"), "Upcoming", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Started"), "Not Started", IF(OR(CONTAINS("Upcoming", CHILDREN()), COUNTIF(CHILDREN(), "Not Started") > 0), "Upcoming", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Canceled"), "Canceled", "Not Started")))))))

Here's the goal of the "IFs" summarized:

If all children are complete, parent is complete

If all children are a combination of complete, canceled and/or on hold, then the parent is complete

if one child is complete or in progress, and the rest are either not started, upcoming, canceled, or on hold, then the parent is in progress

if all children are upcoming, parent is upcoming

if one child is upcoming and the rest are not started, the parent is upcoming

If all children are canceled, parent is canceled

Otherwise, parent is Not Started.

Here are the four scenarios I'm running it issues with:

Parent 1 and Parent 2 should both be complete because we don't want to wait for "On Hold" or "Canceled" tasks

Parent 3 and Parent 4 should be Not Started as the canceled and on hold tasks should not change the status to Upcoming.

Any assistance would be greatly appreciated! All other scenarios I can think of are working as expected. Thank you in advance!

Paul Newcome

Give this a try:

=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = "Canceled"), "Canceled", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "Canceled", @cell = "On Hold")), "Complete", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = "Not Started"), "Not Started", IF(COUNTIFS(CHLDREN(), @cell = "Upcoming")> 0, "Upcoming", "In Progress"))))

Carson Penticuff

These two options can have overlapping logic. Parent 1 and Parent 2 are both affected by this.

If all children are a combination of complete, canceled and/or on hold, then the parent is complete

if one child is complete or in progress, and the rest are either not started, upcoming, canceled, or on hold, then the parent is in progress

Paul Newcome

Give this a try:

=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = "Canceled"), "Canceled", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "Canceled", @cell = "On Hold")), "Complete", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = "Not Started"), "Not Started", IF(COUNTIFS(CHLDREN(), @cell = "Upcoming")> 0, "Upcoming", "In Progress"))))

dhall

@Paul Newcome, you are awesome, as always.

I did find a typo in the last CHILDREN(), you're missing an i.

I fixed that and did some testing but I found the "Upcoming" was overriding "In Progress", so I duplicated that part and put it ahead of the "Upcoming" portion. Here's my revised version:

=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = "Canceled"), "Canceled", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "Canceled", @cell = "On Hold")), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = "Not Started"), "Not Started", IF(COUNTIFS(CHILDREN(), @cell = "Upcoming") > 0, "Upcoming", "In Progress")))))

From my testing, it's working as expected:

And it drastically reduced the length of the formula, this is awesome. Thanks again!

Paul Newcome

Glad you were able to get it sorted.️

And sorry for those couple of mistakes. I hadn't tested it (how I missed the upcoming piece), and sometimes my keyboard likes to freeze up for a second (the missing "I").