IF Statement - Compare many measures with hierarchy

edited 12/09/19 in Smartsheet Basics

Hello - Looking for ideas on a formula to use for the following: I have a column where I am using the red, yellow, green symbols. The Smartsheet is listed by project where multiple projects measures will roll up to one overall customer measure. Each project will have a red, yellow or green symbol for the measure. For the overall customer, I want to say "If any of these projects = "Red", then "red", If any = yellow, then "yellow", otherwise "green". I want that to be the hierarchy as well so if any are red then it would be red. If none are red, but one is yellow, then yellow. Hope that makes sense. Thanks for any help!