# Trying to get a formula to analyze 5 columns and update a 6th column based on the results of the 5

edited 11/20/22

• ✭✭✭✭✭

Its just a big nestled if statement. i.e.

=IF(OR(col1@row="Red",col2@row="Red".......),"Red",IF(OR(countif(col1:col5@row,"Yellow')=1,countif(col1:col5@row,"Yellow')=2),"Yellow",IF(countif(col1:col5@row,"Yellow')>2,"Red",IF(OR(col1@row="",col2@row="".......),"Blue","Green"))))

Something like that. you can sort out the details.

I think there is a problem with your logic though as blue and other catregories are not mutually exclusive. So, when something qualifies as both blue and yellow or red, what colour do you want it to show? If you want it to be blue, you will want IF(OR(col1@row="",col2@row="".......),"Blue" at the start of the formula instead.

• ✭✭✭✭✭

Its just a big nestled if statement. i.e.

=IF(OR(col1@row="Red",col2@row="Red".......),"Red",IF(OR(countif(col1:col5@row,"Yellow')=1,countif(col1:col5@row,"Yellow')=2),"Yellow",IF(countif(col1:col5@row,"Yellow')>2,"Red",IF(OR(col1@row="",col2@row="".......),"Blue","Green"))))

Something like that. you can sort out the details.

I think there is a problem with your logic though as blue and other catregories are not mutually exclusive. So, when something qualifies as both blue and yellow or red, what colour do you want it to show? If you want it to be blue, you will want IF(OR(col1@row="",col2@row="".......),"Blue" at the start of the formula instead.

• Awesome! Thank you so much for your help!

• Awesome! Thank you so much for your help!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!