I need assistance with a parent/child formula

I would like to return a specific value in a parent row when all the values of the children is equal to a specific value.

I would like the parent row to automatically show complete as soon as all the children rows are completed. The cell can be blank if all the values are not complete.

I used the following formula to get the result that I want:

=IF(COUNTIF(CHILDREN(), "Not started") = 3, "Not started", IF(COUNTIF(CHILDREN(), "In progress") > 0, "In progress", IF(COUNTIF(CHILDREN(), "Complete") = 3, "Complete", IF(COUNTIF(CHILDREN(), "Complete") > 0, "In progress"))))

My problem is that you have to physically go and change this formula everytime you add or delete a child row. Is it possible to have another formula that do not use the Countif function?


Best Answer

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Answer ✓

    Have you tried this:

    =IF(COUNTIF(CHILDREN(), "Not started") = COUNT(CHILDREN()), "Not started", IF(COUNTIF(CHILDREN(), "In progress") > 0, "In progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Complete") > 0, "In progress"))))

    I.e. changing the number of rows declared as 3 --> number of rows delared as COUNT(CHILDREN()) instead.

    Hope this helps

    Debbie

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!