Stumped on formula for Status of parent task with multiple children and criteria

Hi All,

I'm trying to write a formula for which the parent task status is based on the following logic with regards to the status of the children:

  • If all child tasks are “not started”, the parent task should be “not started”
  • If all child tasks are “completed”, the parent task should be “completed”
  • Any other combination would be "in progress" (ie if some or all child tasks are "in progress", some are completed some are not started)

Thanks in advance for your help!

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @Amy W

    Put this into the Status column of each Parent row:

    =IF(COUNTIFS(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress")))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!