Logical
AND function in Excel:syntax and examples
AND checks several conditions and returns TRUE only if all of them are met; if one fails, it returns FALSE. In Spanish Excel it is called Y. Syntax: =AND(logical1, [logical2], ...). It is almost always used inside IF.
Syntax
=AND(logical1, [logical2], ...)In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
logical1 | Required. The first condition. Example: B2>=50. |
logical2, ... | Optional. More conditions, up to 255. Example: C2="Yes". |
How do you use the AND function? A step-by-step example
AND checks whether all the conditions are met at once. In this example, to pass you need a 50 or more and you must have submitted the project.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Submitted project |
| 2 | Lucy Grant | 75 | Yes |
| 3 | Mark Rivera | 48 | Yes |
| 4 | Valerie Cruz | 50 | No |
| 5 | Daniel Ford | 92 | Yes |
| 6 | Cameron Hayes | 31 | Yes |
=AND(B2>=50,C2="Yes") returns TRUE for Lucy. For Valerie, =AND(B4>=50,C4="Yes") returns FALSE: she has a 50, but did not submit.
B2>=50is the first condition.C2="Yes"is the second. Text goes in quotes.- AND returns TRUE only if both are met.
To write a result instead of TRUE or FALSE, put it inside IF: =IF(AND(B2>=50,C2="Yes"),"Pass","Fail") returns Pass.
What does AND return in each case?
| Condition 1 | Condition 2 | AND |
|---|---|---|
| TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE |
| FALSE | FALSE | FALSE |
With more conditions the same thing happens: one FALSE is enough. =AND(B2>=50,C2="Yes",B2>=90) returns FALSE for Lucy because her 75 does not reach 90.
A shortcut you will see in other people's sheets: multiplying conditions. =(B2>=50)*(C2="Yes") returns 1 if both are met and 0 if not (for Valerie, 0). It does the same as AND, but reads worse.
AND or OR?
| AND | OR | |
|---|---|---|
| Returns TRUE if | All are met | At least one is met |
| Example with Valerie (50, did not submit) | =AND(B4>=50,C4="Yes") | =OR(B4>=50,C4="Yes") |
| Result | FALSE | TRUE |
| Typical use | Requirements: score and project, age and experience | Alternatives: urgent or important, Boston or Denver |
You can combine them: =OR(AND(B3>=45,C3="Yes"),B3>=50) passes with a 50, or with a 45 if the project was submitted. For Mark (48 and submitted) it returns TRUE.
How does AND show up in an Excel test?
In a job interview it often arrives as a double requirement: "Mark as eligible anyone with more than 2 years of experience and a driver's license." They expect an IF with AND inside, dragged down the whole list.
In a skills test the truth table is common:
If A1 is TRUE and B1 is FALSE, what does
=AND(A1,B1)return? a) TRUE · b) FALSE · c) 1 · d) #VALUE!
Answer: b. AND needs all of them to be TRUE. To count how many rows meet a condition, see COUNTIF.
Practice the AND function
The English exercises are on the way. Until then, copy the scores table into a blank sheet and try each formula above. Change Valerie's answer to Yes and watch the result flip. For a result that is text and not TRUE or FALSE, go back to IFS and IF.
Available in: all versions of Excel. Source: Microsoft: AND function. Results calculated with Formulo's engine.
Common mistakes
- You get TRUE or FALSE instead of text
- AND on its own only answers yes or no. To write Pass or Fail, put it inside IF: =IF(AND(B2>=50,C2="Yes"),"Pass","Fail").
- #VALUE!
- AND finds no condition to evaluate, for example with an empty range or a range of text: =AND(D2:D6) gives #VALUE! if D2:D6 is empty. Write comparisons, such as B2>=50.
- You wrote the between condition the way math does
- 50<=B2<=90 does not work in Excel. It is two conditions: =AND(B2>=50,B2<=90).
Frequently asked questions
What is AND called in Spanish?
Y. In a Spanish Excel: =Y(B2>=50;C2="Yes").
How do I put two conditions in an IF?
Put AND inside the IF if both have to be met: =IF(AND(B2>=50,C2="Yes"),"Pass","Fail"). If one is enough, use OR.
How do I check that a number is between two values?
With two conditions inside AND: =IF(AND(B2>=50,B2<=90),"In range","Outside"). With a score of 75 it returns In range; with 92, Outside.
How many conditions does AND take?
Up to 255. If there are many, think about whether a table or COUNTIFS solves it with less.
Is AND case-sensitive?
No. Text comparisons are not case-sensitive: C2="yes" is TRUE if C2 contains Yes.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.