The AND function
Several conditions in one test: AND requires them all, OR settles for one. Almost always inside an IF.
Syntax
=AND(logical1, [logical2], …)
In a French Excel: =ET(valeur_logique1; [valeur_logique2]; …).
Arguments
- logical1, logical2…: conditions, up to 255.
An example
=IF(AND(B2>=1000,OR(C2="North",C2="East")),"Bonus","")
A bonus for amounts of at least 1,000 in the North or the East.
Pitfalls
- Inside SUMPRODUCT or FILTER, AND and OR do not work row by row: multiply conditions for AND, add them for OR.
- Text passed as a condition returns #VALUE!.
Going further
Frequently asked questions
- Why doesn't AND work inside FILTER?
- AND returns a single value for the whole array. In FILTER, write
(B2:B100>1000)*(C2:C100="North").