I need assistance with a parent/child formula

Jana Brits

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?

Debbie Sawyer

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

Debbie Sawyer

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

Jana Brits

Thank you so much Debbie. This worked!

Debbie Sawyer

Excellent :)

Jana Brits

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", "")

Debbie Sawyer

You could nominate a column in the =COUNT(CHILDREN([Primary Column]@row)) which will always containt a value.