IF AND/OR Nesting

Carrie Tortorella

Hi there!

I would like to combine all of these values into one "Health" column (four colors). See chart attached. How would they be combined?

Price Sheet Health Formulas.docx

Paul Newcome

To nest multiple AND functions inside of an OR function (grouping the "Blue" outputs for example) you treat the entire AND as if it is a single "logical statement" within the OR function.

=IF(OR(AND(..................), AND(...................)), "Blue")

Carrie Tortorella

Thank you @Paul Newcome I appreciate you making it simple for me. I was overthinking this. Would this work? I get an unparseable error.

=IF(OR(AND([Account has Updated Renewal Added to TL]@row = 0, [Round #3 Price Sheet Status]@row = “Not Renewing”),AND([Account has Updated Renewal Added to TL]@row = 1, [Round #3 Price Sheet Status]@row = “Billing Approved”), "Blue”, IF([Price Sheet Deadline]@row >= TODAY(20), "Green", IF([Price Sheet Deadline]@row < TODAY(18), "Red", IF([Price Sheet Deadline]@row < TODAY(20), "Yellow")))

Paul Newcome

It looks like there are a couple of issues.

You are missing a closing parenthesis to finish off the OR function. There are also "smart quotes" in your formula. See how some of your quotes are slanted? Those are call "Smart Quotes" which (ironically enough) are not recognized as valid characters by Smartsheet.

See how some of your quotes as well as all of the quotes in this comment are straight up and down? Those are the ones you want. You can get them by typing directly in Smartsheet, here in the Community, or in a text editor such as Notepad (not Word).

Below is the formula with the closing parenthesis and quotes fixed. If this does not work then we can start diving in a little deeper.

=IF(OR(AND([Account has Updated Renewal Added to TL]@row = 0, [Round #3 Price Sheet Status]@row = "Not Renewing"),AND([Account has Updated Renewal Added to TL]@row = 1, [Round #3 Price Sheet Status]@row = "Billing Approved")), "Blue", IF([Price Sheet Deadline]@row >= TODAY(20), "Green", IF([Price Sheet Deadline]@row < TODAY(18), "Red", IF([Price Sheet Deadline]@row < TODAY(20), "Yellow")))

Carrie Tortorella

That works! Thank you for your help!

Paul Newcome

Happy to help. 👍️