=AND(B2>=50,C2>=50) returns TRUE only when both conditions are true. =OR(B2>=50,C2>=50) returns TRUE when at least one of them is. Inside IF they turn two conditions into one test: =IF(AND(B2>=50,C2>=50),"Pass","Fail").
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Exam | Project | Both 50+ | Either 50+ |
| 2 | Ana | 72 | 64 | Pass | Pass |
| 3 | Ben | 45 | 80 | Fail | Pass |
| 4 | Chloe | 58 | 51 | Pass | Pass |
| 5 | Dan | 38 | 42 | Fail | Fail |
| 6 | Eve | 91 | 47 | Fail | Pass |
Ben and Eve pass one part and fail the other, so they fail column D (AND) and pass column E (OR). Dan fails both and fails both columns. Change Ben's exam in B3 to 55 and he passes in D too.
AND function
=AND(logical1, [logical2], ...)
AND takes up to 255 conditions and returns TRUE only if every one of them is TRUE. One FALSE is enough to make the result FALSE. On its own, AND shows TRUE or FALSE in the cell; inside IF it decides which result IF returns.
Check if a number is between two values
Excel has no BETWEEN function. Use AND with a lower and an upper bound:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sample | Temp | Between 10 and 20 | Status |
| 2 | S1 | 14 | TRUE | OK |
| 3 | S2 | 22 | FALSE | Check |
| 4 | S3 | 10 | TRUE | OK |
| 5 | S4 | 7 | FALSE | Check |
| 6 | S5 | 20 | TRUE | OK |
S3 and S5 sit exactly on the bounds and count as in range, because the tests use >= and <=. To exclude the bounds, use > and <. Keeping the bounds in cells, =AND(B2>=$F$1,B2<=$F$2), lets you change the range without editing the formula. A test like 10<=B2<=20 does not work in Excel: it compares 10<=B2 (TRUE or FALSE) with 20, and Excel ranks TRUE and FALSE above every number, so the result is always FALSE.
OR function
=OR(logical1, [logical2], ...)
OR returns TRUE if at least one condition is TRUE, and FALSE only when all of them are FALSE. It is the usual way to accept several text values:
| A | B | C | |
|---|---|---|---|
| 1 | Order | Region | Zone |
| 2 | 1001 | North | Zone 1 |
| 3 | 1002 | East | Zone 2 |
| 4 | 1003 | South | Zone 1 |
| 5 | 1004 | West | Zone 2 |
| 6 | 1005 | south | Zone 1 |
Text comparisons ignore case, so south in lower case also lands in Zone 1. For a long list of accepted values, =OR(B2={"North","South","Central"}) compares B2 with each item of the array constant in one go.
NOT and XOR
NOT reverses a logical value: =NOT(TRUE) is FALSE. It reads well when the condition you have is the opposite of the one you want: =IF(NOT(C2="Done"),"Follow up","") is the same as =IF(C2<>"Done","Follow up","").
XOR, available since Excel 2013, is the exclusive or: with two conditions it is TRUE when exactly one of them is TRUE, and FALSE when both are TRUE or both are FALSE. With more conditions, it is TRUE when an odd number of them are TRUE.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Task | Owner | Status | Open | One check only |
| 2 | Report | Ana | Done | FALSE | FALSE |
| 3 | Budget | Ben | Open | TRUE | FALSE |
| 4 | Slides | Ana | Open | TRUE | TRUE |
| 5 | Survey | Ben | Done | FALSE | TRUE |
| 6 | Plan | Ana | Done | FALSE | FALSE |
Column E is TRUE for Slides (Ana but not Done) and Survey (Done but not Ana), and FALSE where both or neither hold.
AND and OR together
Conditions can be nested: AND inside OR or OR inside AND. A rep gets a bonus when they are in the North region and either sold more than 1,000 or signed a new client:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | New client | Bonus |
| 2 | Ana | North | 1200 | No | Bonus |
| 3 | Ben | North | 700 | Yes | Bonus |
| 4 | Chloe | South | 1500 | Yes | |
| 5 | Dan | North | 900 | No | |
| 6 | Eve | North | 1100 | Yes | Bonus |
Ana (sales), Ben (new client) and Eve (both) get the bonus. Chloe meets the OR part but is in the South, and Dan is in the North but meets neither OR condition.
Practice: free shipping
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Total | Member | Shipping |
| 2 | 1001 | 35 | Yes |
Your turn: In D2, show "Free" when the order total in B2 is at least 50 or the customer is a member (C2 is "Yes"), otherwise show "Paid".
AND and OR in array formulas: use * and +
AND and OR collapse everything they are given into one TRUE or FALSE. That is what you want in a single row, but not when a formula works on a whole column at once: =AND(B2:B6="North",C2:C6>100) returns one value for the five rows together. In array formulas (FILTER, SUMPRODUCT, SUM over a condition) write the conditions in brackets and multiply them for AND, add them for OR. TRUE counts as 1 and FALSE as 0, so a product is 1 only when both are true, and a sum is at least 1 when either is.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Count | ||
| 2 | Ana | North | 1200 | North and over 1,000 | 2 | |
| 3 | Ben | South | 700 | North or over 1,000 | 4 | |
| 4 | Chloe | North | 900 | |||
| 5 | Dan | South | 1500 | |||
| 6 | Eve | North | 1100 |
F2 counts 2 rows (Ana and Eve), F3 counts 4 (everyone except Ben). In F3 the >0 matters: a row that meets both conditions adds up to 2, and the comparison turns any sum above 0 into one TRUE. The same pattern filters rows, =FILTER(A2:C6,(B2:B6="North")*(C2:C6>1000)); for plain counting with AND logic, COUNTIFS does it without arrays: =COUNTIFS(B2:B6,"North",C2:C6,">1000"), see the COUNTIFS page.
Frequently Asked Questions
How do I use IF with AND in Excel?
Put AND in IF's test: =IF(AND(B2>=50,C2>=50),"Pass","Fail"). The result is Pass only when both B2 and C2 are 50 or more.
How do I check if a number is between two values in Excel?
Use AND with two comparisons: =AND(B2>=10,B2<=20) returns TRUE for any value from 10 to 20, both included. Inside IF: =IF(AND(B2>=10,B2<=20),"In range","Out of range").
What is the difference between AND and OR in Excel?
AND returns TRUE only when every condition is TRUE. OR returns TRUE when at least one condition is TRUE. =AND(TRUE,FALSE) is FALSE and =OR(TRUE,FALSE) is TRUE.
Why does AND not work in my FILTER or SUMPRODUCT formula?
AND and OR reduce a whole range to one TRUE or FALSE instead of one per row. In array formulas multiply the conditions for AND, (B2:B6="North")*(C2:C6>100), and add them for OR.
Can I use AND and OR in the same formula?
Yes, nest one inside the other: =IF(AND(B2="North",OR(C2>1000,D2="Yes")),"Bonus","") requires North plus either sales over 1000 or a Yes in D2.
