Simplify a Formula


Greetings Everyone, Im trying to create a formula that reads across multiple columns. If any of the columns contain a red ryg ball, Id like a "Needs Attention" to be populated. So far, the formula below is helping but Id like to know if it can be simplified.

=IF(OR([Tail Guard]1 = "Red", [Tail Pulley]1 = "Red",[Stairs, Ladder]1 = "Red",[Hand Rail]1= "Red",[Cat Walk]1= "Red",[Bend Pulley Guard]1= "Red", [Bend Pulleys]1="Red",[Counterweight Pulley]1="Red",[Head Pulley Guard]1="Red",[Head Pulley]1="Red",[V Belt Guard]1="Red",[V Belts]1="Red",[Belt Wiper]1="Red",[Splice]1="Red",[Tracking]1="Red",[Idlers]1="Red"), "Needs Attention", "No Further Action Required")

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Are all of the columns next to each other, or are there any columns in between that could contain "Red"?

    You could try counting across all columns and say that if the count of reds is greater than zero (meaning at least one is red) then "Needs Attention".

    =IF(COUNTIFS([Tail Guard]@row:Idlers@row, "Red") > 0, "Needs Attention", "No Further Action Required")



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!