Formula to Capture (R / Y / G) Status Change

Karisa Ely
Karisa Ely ✭✭✭
edited 04/21/20 in Formulas and Functions


I have 4 cells that should trigger the Close cell to change color. The Close cell should stay RED unless ALL 4 cells are GREEN.

I can get the formula to show Green or Red but it will not change based on the 4 cells. What am I missing?

Formula 1:

=IF(COUNTIF(CHILDREN([2]1:[5]1), "Green") = COUNT(CHILDREN([2]1:[5]1)), "Green", "Red")

Formula 2: Gets Close to be Red, but will not stay red until all cells are Green.

=IF([2]1 = "Green", "Green", IF([3]1 = "Green", "Green", IF([4]1 = "Green", "Green", IF([5]1 = "Green", "Green", "Red"))))

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!