Stumped! How to Write IF, AND, OR With Multiple Conditions?

fmorse_apothecary

Hello! I'm trying to write a column formula that will return "YES" if the determined value is "Approved" or "Not Applicable", and return "NO" otherwise.

My formula needs to read across 8 columns, and each column is a dropdown list restricted to 4 values. If all of the columns are values "Not Applicable," or "Approved,", then it should return "YES," however if any of columns are values "Submitted," or "Rejected," then it must return "NO"

"Not Applicable"

"Submitted"

"Approved"

"Rejected"

So with the above in mind, I wrote a nested IF(AND(OR formula, see golden child formula, and it worked great! However once I included all of the columns, see attempt one, Smartsheet warns of a sytanx error and stops the formula from running. I tried adding each piece of the formula one by one, to no avail.

After reading through the help forums, I came upon a user with a similar issue and followed the advice they were given, see attempt two, however this formula fails on me by returning "YES" regardless of the value chosen. I am not familiar with the CONTAINS( function, so I may just be spectaturely messing it up.

I've been staring at smartsheet and notepad for far too long trying to solve this, and I'm quite stumped.

Am I just overthinking? I may be overthinking.

Any recommendations are greatly appreciated.

Golden Child Formula:

=IF(AND(OR([Creative Services Department Coordinator Response]@row = "Approved", [Creative Services Department Coordinator Response]@row = "Not Applicable"), (OR([Engineering Department Coordinator Response]@row = "Approved", [Engineering Department Coordinator Response]@row = "Not Applicable"))), "YES", "NO")

Attempt One:

=IF(AND(OR([Creative Services Department Coordinator Response]@row = "Approved", [Creative Services Department Coordinator Response]@row = "Not Applicable"), (OR([Engineering Department Coordinator Response]@row = "Approved", [Engineering Department Coordinator Response]@row = "Not Applicable"), (OR([Executive Department Coordinator Response]@row = "Approved", [Executive Department Coordinator Response]@row = "Not Applicable"), (OR([Marketing Department Coordinator Response]@row = "Approved", [Marketing Department Coordinator Response]@row = "Not Applicable"), (OR([Quality Department Coordinator Response]@row = "Approved", [Quality Department Coordinator Response]@row = "Not Applicable"), (OR([Regulatory Department Coordinator Response]@row = "Approved", [Regulatory Department Coordinator Response]@row = "Not Applicable"), (OR([Sourcing Department Coordinator Response]@row = "Approved", [Sourcing Department Coordinator Response]@row = "Not Applicable"), (OR([Supply Chain Department Coordinator Response]@row = "Approved", [Supply Chain Department Coordinator Response]@row = "Not Applicable"))))))))), "YES", "NO")

Attempt Two:

=IF(OR(CONTAINS("Approved", [Creative Services Department Coordinator Response]@row), CONTAINS("Not Applicable", [Creative Services Department Coordinator Response]@row), CONTAINS("Approved", [Engineering Department Coordinator Response]@row),CONTAINS("Not Applicable", [Engineering Department Coordinator Response]@row), CONTAINS("Approved", [Executive Department Coordinator Response]@row), CONTAINS("Not Applicable", [Executive Department Coordinator Response]@row),CONTAINS("Approved", [Marketing Department Coordinator Response]@row), CONTAINS("Not Applicable", [Marketing Department Coordinator Response]@row), CONTAINS("Approved", [Quality Department Coordinator Response]@row), CONTAINS("Not Applicable", [Quality Department Coordinator Response]@row), CONTAINS("Approved", [Regulatory Department Coordinator Response]@row), CONTAINS("Not Applicable", [Regulatory Department Coordinator Response]@row), CONTAINS("Approved", [Sourcing Department Coordinator Response]@row), CONTAINS("Not Applicable", [Sourcing Department Coordinator Response]@row), CONTAINS("Approved", [Supply Chain Department Coordinator Response]@row), CONTAINS("Not Applicable", [Supply Chain Department Coordinator Response]@row)), "YES", "NO")

Michael Culley

You have an extra paraenthesis in front of your ORs. You keep typing (OR( but it should be OR(

(for attempt 1)

Michael Culley

You have an extra paraenthesis in front of your ORs. You keep typing (OR( but it should be OR(

(for attempt 1)

Michael Culley

I keep trying to post the full formula but it's not working for some reason

fmorse_apothecary

https://community.smartsheet.com/discussion/comment/339917#Comment_339917

Ah! That fixed it, thank you so much! Once you pointed it out I saw exactly what you meant and pulling all the extra parenthesis out fixed it perfectly.

I've run a couple tests and it's returning everything correctly.

(I'm guessing it won't let you post because it runs past the character count for the comments? )

fmorse_apothecary

If it'll let me post: Here's the fixed formula for anyone who may find it useful!

=IF(AND(OR([Creative Services Department Coordinator Response]@row = "Approved", [Creative Services Department Coordinator Response]@row = "Not Applicable"), OR([Engineering Department Coordinator Response]@row = "Approved", [Engineering Department Coordinator Response]@row = "Not Applicable"), OR([Executive Department Coordinator Response]@row = "Approved", [Executive Department Coordinator Response]@row = "Not Applicable"), OR([Marketing Department Coordinator Response]@row = "Approved", [Marketing Department Coordinator Response]@row = "Not Applicable"), OR([Quality Department Coordinator Response]@row = "Approved", [Quality Department Coordinator Response]@row = "Not Applicable"), OR([Regulatory Department Coordinator Response]@row = "Approved", [Regulatory Department Coordinator Response]@row = "Not Applicable"), OR([Sourcing Department Coordinator Response]@row = "Approved", [Sourcing Department Coordinator Response]@row = "Not Applicable"), OR([Supply Chain Department Coordinator Response]@row = "Approved", [Supply Chain Department Coordinator Response]@row = "Not Applicable")), "YES", "NO")

Michael Culley

Sometimes we just need an extra pair of eyes :)

Michael Culley

Try this

=IF(AND(OR([Creative Services Department Coordinator Response]@row = "Approved", [Creative Services Department Coordinator Response]@row = "Not Applicable"), OR([Engineering Department Coordinator Response]@row = "Approved", [Engineering Department Coordinator Response]@row = "Not Applicable"), OR([Executive Department Coordinator Response]@row = "Approved", [Executive Department Coordinator Response]@row = "Not Applicable"), OR([Marketing Department Coordinator Response]@row = "Approved", [Marketing Department Coordinator Response]@row = "Not Applicable"), OR([Quality Department Coordinator Response]@row = "Approved", [Quality Department Coordinator Response]@row = "Not Applicable"), OR([Regulatory Department Coordinator Response]@row = "Approved", [Regulatory Department Coordinator Response]@row = "Not Applicable"), OR([Sourcing Department Coordinator Response]@row = "Approved", [Sourcing Department Coordinator Response]@row = "Not Applicable"), OR([Supply Chain Department Coordinator Response]@row = "Approved", [Supply Chain Department Coordinator Response]@row = "Not Applicable"))))))))), "YES", "NO")