Status Roll Up Formula

I am working on a sheet to track the status of test cases and want to have the status of the subtask roll up to the parent status. The goal is that if 5 tasks are available and 2 "Passed", 1 "In Progress", 1 "Not Started" and 1 "Failed that it shows the status of "In Progress on the parent row. Below is the formula I am working with and can't get it to work. Any Suggestions?


=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Not Started"), "Not Started", IF(COUNT(CHILDREN()), = COUNTIFS(CHILDREN(), "Failed"), "Failed", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Passed"), "Complete", "In Progress"))))

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Try:

    =IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Not Started"), "Not Started", IF(COUNTIFS(CHILDREN(), "Failed")>0, "Failed", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Passed"), IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Complete"), "Complete", "In Progress"))))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • when I select in progress or completed it shows up blank in the parent row.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!