AND
Returns TRUE only if all conditions are met at once: the target is hit and there are no complaints, the amount is large and payment has arrived.
AND(logical1, [logical2], ...)- [ ]optional part
- logical1
- The first condition, for example B2>=100000.
- logical2
- The second condition. You can add more conditions after it, separated by commas.Optional
Return value
TRUE if all conditions are met, and FALSE if at least one isn’t.
Examples
Bonus for hitting target with no complaints
fx
A
B
C
D
E
1
2
3
4
5
6
7
8
Rep
Sales, $
Complaints
Bonus
Harris
125000
0
Lewis
132000
2
Walker
87000
0
Young
100000
0
King
64000
1
Sheet1
Details
AND takes several conditions separated by commas. The answer is TRUE only if every one of them holds. One unmet condition is enough to make the answer FALSE.
On its own, AND returns only TRUE or FALSE. It’s usually placed inside IF as the condition, to get a clear answer: “Bonus” or “No”.
Common mistakes
- A “from … to” condition is written as two comparisons: AND(B2>=10,B2<=20). Writing 10<=B2<=20 doesn’t work correctly.
- AND and OR get mixed up: AND requires all conditions at once, OR needs just one.
- Text in a condition without quotes: C2=Yes instead of C2="Yes" gives a #NAME? error.