Combined IF, AND, & OR Statments

✭✭
edited 05/05/22

I am having trouble with a combined IF, AND, & OR formula. The formula is to check five columns for a Yes "Y" value. For two of the columns only one value has to be Yes. If all are true then return a 1, if not then return a 0. Where is my formatting wrong?

=IF(AND([SPC Received]@row = "Y", OR([SDS Received]@row = "Y", [Shelf Life Received]@row = "Y"), [Code Received]@row = "Y", [Code Created]@row = "Y") = true, 1, 0)

Tags:

• ✭✭✭✭✭✭

You don't need the '= true' part of your formula

=IF(AND([SPC Received]@row = "Y", OR([SDS Received]@row = "Y", [Shelf Life Received]@row = "Y"), [Code Received]@row = "Y", [Code Created]@row = "Y"), 1, 0)

• ✭✭

Thanks so much, I removed the '=True', and it worked.

• ✭✭✭✭✭✭

The problem with this formula is that you put the OR statement in the middle of the AND statement. I don't believe those two can be used with each other. For example, you can't do =IF(AND(OR(logic) and you can't thread the OR in the AND statement until the AND is closed out and another IF is begun.

If you write out which columns need a "Y" to return a "1" and which need 2 columns with "Y" to return a "1" etc, then I can help you write the formula.

• ✭✭✭✭✭✭

You don't need the '= true' part of your formula

=IF(AND([SPC Received]@row = "Y", OR([SDS Received]@row = "Y", [Shelf Life Received]@row = "Y"), [Code Received]@row = "Y", [Code Created]@row = "Y"), 1, 0)

• ✭✭