#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Determine RYG ball color based on rows below

Options
edited 12/09/19

Hello,

I'm trying to write a formula that'll allow me to look at the color of the  RYG balls in some rows, find the lowest color and set that as the main color for the overall project.

So, Project A needs a status. I'd like to look at tasks 1-5; if any of them are red, the entire project status needs to be red. If any of the tasks are yellow, the entire project status needs to be yellow and if the tasks are all green, the entire project status needs to be green.

I started with this in the cell that represents the entire project status but I got an 'invalid operation' error.

=IF(Status2:Status20 = "Green", "Green").

Thank you,

Reema

Tags:

• ✭✭✭✭✭
Options

You could definitely do this! I would use the COUNTIF function to count the number of each color and set the current cell appropriately. In this example, I'm setting the color of [Call Status]1 by looking at [Call Status]2:[Call Status]9

=IF(COUNTIF([Call Status]2:[Call Status]9, "Red") > 0, "Red", IF(COUNTIF([Call Status]2:[Call Status]9, "Yellow") > 0, "Yellow", "Green"))

Basically the formula goes in two steps:

1) If any of these rows is red, make it red. Otherwise, count the yellows.

2) If any of these rows is yellow, make it yellow. Otherwise, make it green.

Let me know if something isn't clear, or if that doesn't solve your situation!

• Options

That did it! Thank you Greg

• ✭✭✭✭✭✭
Options

worked for me too, thank you.

This discussion has been closed.