RYG Parent/Child Row Status Correlation

Hello,

I'm trying to have a formula in my parent row status (red, yellow, green) where it will answer with a "summary" of all the children status.

For example, if all 5 children rows were red then the parent row status would turn red.

If 3 children rows were yellow and 2 children rows were red then the parent row status would turn yellow.

But, I'm trying to get the formula to also take into account blanks. Right now blanks are counted as reds, but I would like them to be counted as blanks.

So, if 5 children rows were red and 7 children rows were blank then the parent row status would turn blank.

Is this possible????

THANK YOU!!!

Find more posts tagged with

Formulas

Comments

Adam Overton

It is. You can get the numbers by using COUNTIF(CHILDREN(), "Red") and "Green" and "Yellow". With those counts you can determine how you want to format them.

Sometimes it's easier to build some extra columns to get those summary separately, and then pull the summary together in the actual parent column. Once you get it working, you can decide if you want to combine into one giant formula or just hide the other columns.

Kara Lumley

This might help you get started - if most of the children are red, make the parent red, if most of the children are green, make the parent green, otherwise, make the parent yellow.

=IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow"))

You could add in a condition for " " (indicating blank) or replace yellow with " " .

Best,

Kara

ruizdeaz36321

Hi Kara, I need my parent row to show a blue ball when the children are blank, red for everything else. I'm probably slow now because I have tried so many options and built so many formulas today.

This is the last I tried but I can't see what I did wrong:

IF(ISBLANK(COUNTIF(CHILDREN(), " ")), "Blue", "Red")

Many thanks in advance!

Kara Lumley

Hello - give this a try:

=IF(COUNTIF(CHILDREN(), ISBLANK(@cell)) > 0, "Blue", "Red")