AND and OR in Excel: how to use it
AND gives TRUE if all conditions are met. OR gives TRUE if at least one is. You usually use them inside IF.
Try the first exercises for freeIn the lesson AND, OR and NOT
What do AND and OR do?
AND and OR combine several conditions into one answer. AND gives TRUE if all the conditions are true. OR gives TRUE if at least one of them is. NOT reverses an outcome: it turns TRUE into FALSE.
On their own, they only show TRUE or FALSE. Their strength lies in IF: put AND or OR where the test goes, and IF chooses an outcome based on several conditions at once.
When do you use AND and OR?
- A bonus if the region is Leeds and the revenue is over 1000: =IF(AND(A2="Leeds",C2>1000),"Bonus","None").
- Free shipping for members or for orders of 50 or more: =IF(OR(A2="Member",B2>=50),0,4.95).
- Checking whether a number is between two limits: =AND(B2>=10,B2<=20).
- Everything except one value: =NOT(A2="Leeds").
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
TRUE if all the conditions are true.
Filled in: =AND(A2="Leeds",C2>1000)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
AND | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | AND |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
logical1 | The first condition, such as A2="Leeds". | A2="Leeds" |
[logical2] | Optional. Another condition, such as C2>1000. | C2>1000 |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. | |
... | You can give more, with a comma between each one. |
AND and OR step by step
E1 contains =AND(A2="Leeds",C2>1000). Both conditions are true for row 2. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- In E1, type AND with a bracket.
=AND( - The first condition: is Leeds in A2? Type a comma.
=AND(A2="Leeds", - The second condition: is the revenue in C2 greater than 1000?
=AND(A2="Leeds",C2>1000 - Close the bracket and press Enter: both are true for row 2, so you see TRUE.
=AND(A2="Leeds",C2>1000)
More examples
AND in IF
E1 contains =IF(AND(A2="Leeds",C2>1000),"Bonus","None"). Leeds and more than 1000: both conditions are true, so Bonus. Change C2 to 900, and it becomes None.
OR for free shipping
C2 contains =IF(OR(A2="Member",B2>=50),0,4.95). The order is under 50, but the customer is a member: with OR, one condition is enough.
Between two limits
C1 contains =AND(A2>=15,A2<=25). 18 is between 15 and 25, so TRUE. Write the two limits separately: 15<=A2<=25 doesn't work in Excel.
Combining with IF
Put AND or OR where the test goes: =IF(AND(A2="Leeds",C2>1000),"Bonus","None").
Where it often goes wrong
- OR(A2="Leeds","York") doesn't work. Repeat the cell: OR(A2="Leeds",A2="York").
Practise this in the lesson AND, OR and NOT. The first exercises are free.
Frequently asked questions
What does NOT do?
NOT turns a test around: TRUE becomes FALSE and the other way round.