My Formula Needs Help!

Kym L

Hi, I have a status column with the following value: Not Started, In Progress, Canceled, On Hold, Complete.

I would like to modify my current formula to cause the behavior below:

• All children are Complete – Parent = Complete
• One or more children are Complete, In Process, and any other status – Parent = In Process
• All children are On Hold – Parent = On Hold
• All children are Not Started – Parent = Not Started
• All children are Canceled – Parent = Canceled

My current formula looks like this:

=IF(OR(COUNT(CHILDREN(Subject@row)) = 0, Subject@row = ""), "", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIF(CHILDREN(), "On Hold") = COUNT(CHILDREN()), "On Hold"))))))

Thanks!

Paul Newcome

Try this...

=IF(COUNTIFS(CHILDREN(), @cell = "Cancelled") = COUNT(CHILDREN(Subject@row)), "Cancelled", IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNT(CHILDREN(Subject@row)), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN(Subject@row)), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN(Subject@row)), "Not Started", "In Process"))))

Paul Newcome

Try this...

=IF(COUNTIFS(CHILDREN(), @cell = "Cancelled") = COUNT(CHILDREN(Subject@row)), "Cancelled", IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNT(CHILDREN(Subject@row)), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN(Subject@row)), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN(Subject@row)), "Not Started", "In Process"))))

Kym L

Thanks Paul! For the most part this is working beautifully. The only glitch is when one or more of the children are Cancelled and the others are Complete, the Parent is showing as In Progress and would like Parent to show Complete in this case

Paul Newcome

Ok. Here is a little tweak:

=IF(COUNTIFS(CHILDREN(), @cell = "Cancelled") = COUNT(CHILDREN(Subject@row)), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "Cancelled")) = COUNT(CHILDREN(Subject@row)), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN(Subject@row)), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN(Subject@row)), "Not Started", "In Process"))))

Kym L

thanks!!!! perfect :-)

Paul Newcome

Happy to help. 👍️