Count Children If

Natalie Gorman

Hi There

I have a status column using the ryg balls Red, Yellow, Green and Blue.

I currently have a formula which will return Red, Yellow, Green and Blue if all the children are the respective colour.

=IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Yellow"))

However it really needs to exclude the Blue, unless all the children rows are Blue.

Eg. If the parent has two children rows and one is green and one is blue, I want the parent row to show green, and at the moment it is showing yellow.

The child rows currently have a different formula which is not ideal, so the utopia would be to have one formula which would also include the following.

=IF([Task Not Required this Year]23 = true, "Blue", IF(Progress23 = "", "", IF(Progress23 = "Quarter", "Yellow", IF(Progress23 = "Half", "Yellow", IF(Progress23 = "Three Quarter", "Yellow", IF(Progress23 = "Full", "Green", "Red"))))))

I have been going around in circles with this for a few weeks now, so would really appreciate some help.

I am wondering if I could do this with an automation, so I am also starting to explore this as an option and again, any help in this would be gratefully received.

Many thanks

Natalie

Paul Newcome

For the Parent row formula, you can start off with an IF to say that if all children are blue, then blue.

=IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Yellow"))

For the second part of your question, you can use an IF statement to say that if the row is a parent row, run the parent row formula, otherwise run the child row formula.

=IF(COUNT(CHILDREN()) > 0, parent_row_formula, child_row_formula)

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Yellow")), IF([Task Not Required this Year]23 = true, "Blue", IF(Progress23 = "", "", IF(Progress23 = "Quarter", "Yellow", IF(Progress23 = "Half", "Yellow", IF(Progress23 = "Three Quarter", "Yellow", IF(Progress23 = "Full", "Green", "Red")))))))

Natalie Gorman

Thank you @Paul Newcome for your reply, I really appreciate your help.

In the image attached, the parent is Pay and Benefits. All children are green except for Pay Review.

The formula is currently returning yellow for the parent row, when what I want it to do is return green. So it effectively ignores the blue (unless all children are blue).

So it should only ever return blue when all are blue, yellow when the children are a mixture of the colours, and Red and Green when either all are Red or a mixture of Red and blue or all are Green and a mixture of Green and Blue.

Does that make sense?

Many thanks

Natalie