The IF + AND, OR guide
When a rule mixes "all of these" with "any of those", put an OR inside an AND and the whole test inside IF: =IF(AND(C2>=100, OR(B2="A", B2="B")), 0, 7.5).
Build it from the inside out
Write the rule as a sentence first: free delivery when the order is $100 or more and it goes to zone A or zone B.
- The zone part:
OR(B2="A",B2="B"). Type it in a spare cell and check that it shows TRUE where you expect. - Add the amount:
AND(C2>=100, OR(B2="A", B2="B")). - Wrap it in IF:
=IF(AND(…),0,7.5).
If you've only used AND and OR on their own, this is the next step: the same functions, one inside the other.
Where the brackets close
Each function closes after its last test. IF(AND(C2>=100), OR(…), 7.5) closes the AND after one test, so IF takes the OR as the value to show and you get TRUE or FALSE instead of a charge. Excel colours each pair of brackets as you type; follow the colours.
AND(OR) is not OR(AND)
AND(C2>=100, OR(B2="A", B2="B")) needs $100 for both zones. OR(AND(C2>=100, B2="A"), B2="B") needs $100 in zone A only, so a $45 order to zone B goes free. Try the rows near the edges: exactly $100, just under, and an order to a zone outside the list.
Common mistakes
- OR(B2="A","B"). "B" on its own isn't a test, so Excel gives #VALUE!. Each test needs the cell:
B2="B". - AND for the zones.
AND(B2="A",B2="B")is never TRUE: a cell can't hold two values. - OR for everything. One true test is enough for OR, so big orders to any zone go free.
- > instead of >=. "$100 or more" includes $100.
FAQ
- How do I use IF with AND and OR together in Excel?
- Put the OR inside the AND and the AND inside IF: =IF(AND(C2>=100,OR(B2="A",B2="B")),"Yes","No").
- How do I check if a cell is one of several values?
- Use OR with one comparison per value: OR(B2="A",B2="B",B2="C").
- Why does OR(A1="x","y") give #VALUE!?
- The second argument is just the text "y", not a test. Write A1="y" so each argument compares the cell.
- How many conditions can AND and OR take?
- Up to 255 each, and they can be nested inside each other and inside IF.



































































