RYGB formula that isn't working when Status is yellow (child row)

Peggy Parchert

I have a sheet that has a column (Status) that is the RYGB symbols.

The Status key is:

Blue = Completed

Green = In Progress

Yellow = On Hold

Red = Not Started

There are two columns in the sheet:

Status (symbol column - RYGB)

Task/Description (Text/Number column) - each task has several subtasks (child rows)

I have the following formula in the Status column at the Parent row for each Task and then at the Parent row at the top of the sheet (line 1):

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

We just update the child row RYGB ball to the appropriate color - no dates, % Complete etc.

However, it's not working correctly. Is the order of colors incorrect?

Any suggestions would be greatly appreciated.

Thanks -Peggy

Mark Cronk

That helps. Try this:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Yellow") >0, "Yellow", IF(COUNTIFS(CHILDREN(), "green") >0, "green", IF(COUNTIFS(CHILDREN(), "Blue")=COUNT(CHILDREN()) , "blue", "Red"))))

Mark

Mark Cronk

Hi Peggy,

Your formula will return Red if all of the children aren't the same color. It's that what you want?

The formula below will return blue if all are blue(compete). Red if all are red (not started). Yellow if more than half are on hold. Green if none of the others are true.

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Yellow")>=0.5*COUNT(CHILDREN()) , "Yellow", "Green"))))

What you need?

Mark

Peggy Parchert

@Mark Cronk

I need it to show Yellow if one of the child rows is Yellow; Blue if all child rows are Blue; Green if one of the child rows are Green and Red if none of other colors are present. I've mocked up a sheet for a visual.

We update the child row - so the formula is only in the Parent row (those rows that are locked).

Appreciate the help.

Peggy

Mark Cronk

That helps. Try this:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Yellow") >0, "Yellow", IF(COUNTIFS(CHILDREN(), "green") >0, "green", IF(COUNTIFS(CHILDREN(), "Blue")=COUNT(CHILDREN()) , "blue", "Red"))))

Mark

Peggy Parchert

@Mark Cronk

Apologize for the late response. That worked! Thank you so much!!!

Mark Cronk

Hi Peggy,

Glad you found a solution. Thank you for contributing to the community.

Mark