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

✭✭✭✭✭✭

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)

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

• ✭✭✭✭✭✭

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

Hi Peggy,

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

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!