Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
IF with AND
O101 is from Lagos and worth 70, so both tests pass and the sheet shows Star.
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 | Location | 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.
C2="Lagos"One test: is this cell Lagos? Each test goes inside AND, separated by a comma.
IF(AND(...),"Star","Normal")Put AND where IF's test goes.
Put together: =IF(AND(C2="Lagos",F2>50),"Star","Normal").
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 Bulk if the quantity is 3 or more AND the order is from Lagos, otherwise Normal.
=IF(AND(E4>=3,C4="Lagos"),"Bulk","Normal")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 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")5. In G8, show Bulk if the order is from Abuja AND the quantity is 4 or more. Otherwise show Normal.
=IF(AND(C8="Abuja",E8>=4),"Bulk","Normal")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 →