# My Formula Needs Help!

Options

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!

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

thanks!!!! perfect :-)

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!