Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
IF with AND and OR
O101 is from Lagos and worth 70, so both tests pass and the sheet shows Star. OR works the same way, but only needs one test to pass.
Try this lesson free →Example
AND is TRUE only when every test inside it is true. Use it as the test inside IF:
=IF(AND(C2="Lagos",F2>50),"Star","Normal")| Order | Day | Store | Product | Qty | Revenue | Label |
|---|---|---|---|---|---|---|
| O101 | Mon | Lagos | Earbuds | 2 | 70 | |
| O102 | Mon | Abuja | Charger | 1 | 15 | |
| O103 | Tue | Lagos | Phone case | 3 | 24 | |
| O104 | Wed | Lagos | Earbuds | 1 | 35 | |
| O105 | Wed | Abuja | Earbuds | 4 | 140 | |
| O106 | Thu | Lagos | Charger | 2 | 30 | |
| O107 | Fri | Abuja | Phone case | 5 | 40 | |
| O108 | Fri | Lagos | Earbuds | 3 | 105 |
Key points
AND(test1, test2)TRUE only if every test is true.
OR(test1, test2)TRUE if at least one test is true.
IF(AND(...), ..., ...)Put AND or OR where IF's test goes.
Put together: =IF(AND(C2="Lagos",F2>50),"Star","Normal"), or with OR: =IF(OR(D2="Earbuds",F2>100),"Promote","Skip").
Practice
1. In G3, show Priority if the order is from Abuja AND worth over 100, otherwise Normal.
=IF(AND(C3="Abuja",F3>100),"Priority","Normal")2. In G4, show Promote if the product is Earbuds OR the revenue is over 100, otherwise Skip.
=IF(OR(D4="Earbuds",F4>100),"Promote","Skip")3. In G6, show Priority if the order is from Abuja AND worth over 100. Otherwise show Normal.
=IF(AND(C6="Abuja",F6>100),"Priority","Normal")4. In G9, show Promote if the product is Earbuds OR the revenue is over 100. Otherwise show Skip.
=IF(OR(D9="Earbuds",F9>100),"Promote","Skip")5. In G7, show Check if the order is from Lagos AND has a quantity of 2 or more. Otherwise show OK.
=IF(AND(C7="Lagos",E7>=2),"Check","OK")Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.
Try this lesson free →