Nested If/And but need and And/Or


I am trying to have the Opportunity Status ball automatically turn green when the four columns to the right are Green OR Gray. I can get the formula to work with just "Green", but when I try to put the OR "Gray" into the formula, it results in a yellow ball or unparseable.

=IF(AND([Commercial Business Case Completed]1 = "Green", [Technical Case Provided]1 = "Green", [Legal Documents Provided]1 = "Green", [CSR's Provided]1 = "Green"), "Green", "Yellow")

Yellow ball results with following formula:

=IF(AND([Commercial Business Case Completed]@row = "Green", [Commercial Business Case Completed]@row = "Gray", [Technical Case Provided]@row = "Green", [Technical Case Provided]@row = "Gray", [Legal Documents Provided]@row = "Green", [Legal Documents Provided]@row = "Gray", [CSR's Provided]@row = "Green", [CSR's Provided]@row = "Gray"), "Green", "Yellow")

#Unparsable Result for formula below:

=IF(AND([Commercial Business Case Completed]9 = "Green"or='Gray", [Technical Case Provided]9 = "Green"or="Gray", [Legal Documents Provided]9 = "Green"or="gray", [CSR's Provided]9 = "Green"or="Gray"), "Green", "Yellow")

Thank you in advance for any help.

Best Answer


  • ron.judenberg112096

    =IF(AND(OR([Commercial Business Case Completed]9 = "Green", [Commercial Business Case Completed]9 = "Gray"), OR([Technical Case Provided]9 = "Green", [Technical Case Provided]9 = "Gray"), OR([Legal Documents Provided]9 = "Green", [Legal Documents Provided]9 = "gray"), OR([CSR's Provided]9 = "Green", [CSR's Provided]9 = "Gray")), "Green", "Yellow")

  • ShariM
    ShariM ✭✭

    Perfect. It worked just as I intended. Thank you.

  • Genevieve P.
    Genevieve P. Employee Admin

    Happy to help! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!