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

Reema Hussain
edited 12/09/19 in Archived 2016 Posts



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").


Can someone please help?


Thank you,




  • Greg Gates
    Greg Gates ✭✭✭✭✭

    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!

  • Reema Hussain

    That did it! Thank you Greg Smile

  • Si Spence
    Si Spence ✭✭✭✭✭✭

    worked for me too, thank you.

This discussion has been closed.