Logical
When both tests must pass
Definition
Showing 1 only when two tests both pass means wrapping AND inside IF.
Syntax
=IF(AND(logical1, logical2),1,0)
Arguments
| Argument | Meaning |
|---|---|
logical1 |
The first test, for example B2>=10. |
logical2 |
The second test, for example C2<=3. |
Worked example
A shop flags a restock when units in B2 are at least 10 and days left in C2 are at most 3.
| A: Item | B: Units | C: Days left |
|---|---|---|
| Flour | 15 | 2 |
| Yeast | 6 | 1 |
| Salt | 20 | 9 |
Formula: =IF(AND(B2>=10,C2<=3),1,0)
Result: 1. 15 is at least 10 and 2 is at most 3, so both tests pass.
Practice
Cell C2 holds 4. Cell D2 holds 9. In E2, write a formula that shows 1 when C2 is greater than 10 and D2 is less than 5, and 0 otherwise.
- In E2, write an IF formula that uses AND and shows 1 when C2 is greater than 10 and D2 is less than 5, and 0 otherwise.
- E2 should show the result for 4 and 9.
Step 1 of 2
Enter =IF(AND(C2>10,D2<5),1,0) in E2.
C2 is 4, which is not greater than 10, and D2 is 9, which is not less than 5, so the result is 0.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
E2 uses AND.
E2 now shows 0.
The next lesson is When either test may pass.
Related functions
AND is the function that requires both tests to pass.
Common mistake
Writing only one test, as in =IF(AND(B2>=10),1,0): days left is ignored, so Salt would also show 1.