Function guides · Logical
AND function
Returns TRUE only if every condition is true.
Syntax
=AND(logical1, [logical2], ...)
| Argument | What it is |
|---|---|
logical1 | A condition that evaluates to TRUE or FALSE. |
logical2, ... | Optional. Up to 255 more conditions. |
What AND does
AND checks several conditions and returns TRUE only when every one of them is true. If even one is false, the answer is FALSE.
It rarely appears alone. The usual home for AND is the first argument of IF, where it lets one decision depend on two or more facts, such as sales and rating, or date and status.
OR, which has its own page, is the mirror image: it needs only one condition to be true.
A useful way to read an AND formula is to say it aloud with “and” between the conditions. =AND(B2>10000,C2>=4) becomes “sales over 10,000 and rating at least 4”. If the sentence you want has “or” in it, you want OR instead.
AND evaluates every condition even after one has failed, so it will not protect a later condition from an error. If C2 might hold an error, the whole AND returns that error. Excel has no short-circuit version, so guard the cell first with IFERROR or ISNUMBER.
AND examples
1. A bonus that needs two things
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Rating | |
| 2 | Mei | 12500 | 4.5 | TRUE |
| 3 | Omar | 14000 | 3.2 |
Both conditions must hold: sales above 10,000 and a rating of 4 or more. Mei meets both, so AND returns TRUE.
On its own, AND returns TRUE or FALSE. That is usually fed into IF.
2. One condition fails, so the whole thing fails
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Rating | |
| 2 | Mei | 12500 | 4.5 | |
| 3 | Omar | 14000 | 3.2 | FALSE |
Omar's sales clear the bar, but his rating does not. A single FALSE makes AND return FALSE, however many conditions pass.
3. Inside IF to return words
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Rating | |
| 2 | Mei | 12500 | 4.5 | Bonus |
AND on its own gives a bare TRUE or FALSE. Placed in the test of an IF, it picks between two labels.
The same pattern with OR makes the label appear when any one condition is true.
Common AND mistakes
- Writing a range test like math class.
=AND(10<B2<20)does not work. Excel evaluates 10<B2 first, gets TRUE or FALSE, then compares that to 20. WriteAND(B2>10,B2<20). - Using AND where OR is needed. "Weekend" means Saturday or Sunday.
AND(WEEKDAY(A2)=7,WEEKDAY(A2)=1)can never be true. Use OR. - Comparing text with the wrong quotes.
=AND(A2=West,B2>100)returns #NAME?. Text in a comparison needs quotes: A2="West". - Using AND on a range by accident.
=AND(B2:B10>5)is an array test and needs dynamic arrays or Ctrl+Shift+Enter in older Excel. Test one cell at a time or use COUNTIFS.
AND on Excel job tests
The words that signal AND are "and", "both" and "all of". "Flag a row as Review when the amount is over 5,000 and the approver is blank" is IF with AND in its first argument.
Keep each condition as its own short comparison. A cell that must be empty can be tested with C2="" or ISBLANK(C2). To check the formula, try three rows by hand: one that meets every condition, one that meets only the first, and one that meets only the second. The middle cases are where AND and OR get swapped.
Related functions
AND questions
- What is the difference between AND and OR?
- AND is TRUE only when every condition is TRUE. OR is TRUE when at least one condition is TRUE.
- Can AND take more than two conditions?
- Yes, up to 255. Separate them with commas.
- How do I use AND inside IF?
- Place it in the first argument:
=IF(AND(B2>10,C2<5),"Yes","No"). - Is there an AND for text criteria across a range?
- COUNTIFS covers that. It counts rows where several conditions all hold, without writing AND.
Practice AND
The lesson AND: every condition must hold (module 7 of Everyday Excel) has checked AND exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample