AND and OR functions in Excel
Last updated
AND returns TRUE when all its conditions are true; OR returns TRUE when at least one is. They are most useful as the test of IF: =IF(AND(B2>15000,C2>=4),250,0) pays a 250 bonus only when sales are above 15,000 and the rating is at least 4.
AND and OR syntax
=AND(logical1, [logical2], …) =OR(logical1, [logical2], …)
- logical1
- The first condition, usually a comparison such as
B2>15000orC2="Yes". - logical2, …
- More conditions, separated by commas. AND needs all of them to be TRUE, OR needs only one. You can list up to 255.
Practice AND and OR here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Paid and shipped
An order can be closed once it is paid and shipped. In D2, write an AND that returns TRUE when B2 and C2 are both Yes, then copy it down to D6.
| Order | Paid | Shipped | Ready to close |
| SO-1041 | Yes | No | |
| SO-1042 | Yes | Yes | |
| SO-1043 | No | Yes | |
| SO-1044 | Yes | Yes | |
| SO-1045 | No | No |
How to combine conditions, step by step
- Write each condition on its own first, for example
B2>15000andC2>=4. - Put them inside
AND(if all must be true, orOR(if one is enough, separated by a comma. - Press Enter to see TRUE or FALSE, and check a few rows by hand.
- To show your own result, use that AND or OR as the first argument of IF:
=IF(AND(…),250,0).
AND and OR examples
Paid and shipped
=AND(B2="Yes",C2="Yes")
TRUE only when both cells say Yes. A single No gives FALSE.
Free shipping
=OR(B2>=50,C2="Yes")
TRUE when the order is at least 50 or the customer is a member.
Flag a problem project
=IF(OR(B2>0,D2>C2),"Review","On track")
Shows Review when the project is late or over budget.
OR inside AND
=IF(AND(OR(B2>=2,C2="Yes"),D2=0),"Net 30","Prepay")
Two years as a client or a guarantor, and no unpaid invoices: the OR is one of the AND’s conditions.
Common AND and OR mistakes and how to fix them
| Result | Cause | Fix |
|---|---|---|
| Always FALSE | AND used where one condition is enough, or a range such as B2:B6>0 where you meant one cell: AND then needs every row of the range to pass. | Use OR when any condition will do, and test one row at a time: B2>0. |
| #VALUE! | The arguments are text cells, not comparisons, e.g. =AND(A2,B2) where both cells hold names. AND ignores text, and with nothing left to test it returns #VALUE!. | Write a full comparison for each argument: A2="Yes". |
| Wrong result at the limit | > and >= mixed up, so a value exactly on the threshold falls on the wrong side. | Read the rule again: “above” is >, “at least” is >=. |
| #NAME? | Text without double quotes, e.g. C2=Yes. | Put text in double quotes: C2="Yes". |
AND, OR, NOT and nested IF
AND and OR are not the same as typing several IFs. Nested IFs pick one result among several bands, while AND and OR combine conditions into a single TRUE or FALSE for one decision. For “not”, wrap a condition in NOT: =AND(B2>0,NOT(C2="Closed")). When the conditions are meant to count or add rows, COUNTIFS and SUMIFS already combine them with AND logic.
Frequently asked questions
How do I use IF with two conditions?
Put both conditions in AND (both must be true) or OR (one is enough) and use it as the test: =IF(AND(B2>15000,C2>=4),250,0).
Can I mix AND and OR?
Yes, nest one inside the other. =AND(OR(B2>=2,C2="Yes"),D2=0) means “B2 at least 2 or C2 is Yes, and D2 is 0”.
Are AND and OR case-sensitive with text?
No. C2="yes" is TRUE for Yes, YES or yes.
How many conditions can AND take?
Up to 255. Past three or four, consider a helper column per condition so the formula stays readable.
Keep practising
All 5 AND and OR exercises, with explanations and progress tracking.
Open the AND and OR practice set