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:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    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"))))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!