IF-OR-AND Formula for Multiple Scenarios

✭✭✭✭

Help! I need to finalize a formula to return a value of "Yes" if one of two AND statements are true. I am not getting an error with the formula below, but it is not returning a value of Yes or No:

=IF(OR(AND([Send to Compliance?]@row = 1, [Finance Approval Status]@row = "Approved", (AND([Send to Compliance?]@row = 0, [Finance Approval Status]@row = "Approved")), "Yes", "No")))

Tags:

• ✭✭✭✭✭✭

Your parenthesis are off. Make sure you are keeping your AND statements separate within the OR statement.

=IF(OR(AND(..........), AND(..........)), "Yes", "No")

• ✭✭✭✭✭✭

This goes back to my first response about the parenthesis being off. Double check your syntax.

• ✭✭✭✭✭✭

Your parenthesis are off. Make sure you are keeping your AND statements separate within the OR statement.

=IF(OR(AND(..........), AND(..........)), "Yes", "No")

• ✭✭✭✭
edited 12/14/23

Thanks @Paul Newcome! That worked :)

• ✭✭✭✭

@Paul Newcome one last question. The formula returns "No" when one of the AND statements is complete. Do I have the right logic? I want it to return Yes if either scenario applies and No if neither applies.

• ✭✭✭✭✭✭

What is the logic you want it to follow?

• ✭✭✭✭

@Paul Newcome I want to follow the logic below:

Return "Yes" if EITHER of the following is accurate:

1. The box is checked for "Send to Compliance" AND "Finance Approval Status" is Approved
2. The box is unchecked for "Send to Compliance" but "Finance Approval Status" is Approved

If not, then return "No."

=IF(OR(AND([Send to Compliance?]@row = 1, [Finance Approval Status]@row = "Approved", (AND([Send to Compliance?]@row = 0, [Finance Approval Status]@row = "Approved")), "Yes", "No")))

• ✭✭✭✭✭✭
edited 12/19/23

So really... It doesn't matter whether the [Send To Compliance] box is checked or unchecked as long as the [Finance Approval Status] is "Approved"?

=IF([Finance Approval Status]@row = "Approved", "Yes", "No")

• ✭✭✭✭

@Paul Newcome the issue is that I have another workflow that triggers from the checkbox. In the other workflow, our Compliance team is notified when the box is checked AND Financial Approval is "Approved." But, I think I just thought of a solution that uses a status in another column to trigger this. Stay tuned...

• ✭✭✭✭

@Paul Newcome I adjusted the formula with an OR/AND based on a new set of criteria to be clearer. But, I'm receiving an error message:

=IF(OR(AND([Compliance Approval Status]@row = "N/A", [Finance Approval Status]@row = "Approved", AND([Compliance Approval Status]@row = "Approved", [Finance Approval Status]@row = "Approved", "Yes", "No")))))

• ✭✭✭✭✭✭

This goes back to my first response about the parenthesis being off. Double check your syntax.

• ✭✭✭✭

Thanks @Paul Newcome! I corrected it and it now works:

=IF(OR(AND([Compliance Approval Status]@row = "N/A", [Finance Approval Status]@row = "Approved"), AND([Compliance Approval Status]@row = "Approved", [Finance Approval Status]@row = "Approved")), "Yes", "No")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!