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

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

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

Thank you so much Debbie. This worked!

Excellent :)

I noticed that all the children has to have a value in the sell for the formula to work. No cell can be blank.
The formula in the 100% cell: =(COUNTIF(CHILDREN(), "Complete") / COUNT(CHILDREN()))
The formula in the Complete cell: =IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "")

You could nominate a column in the =COUNT(CHILDREN([Primary Column]@row)) which will always containt a value.
Help Article Resources
Categories
Check out the Formula Handbook template!