How to Use AND, OR and NOT in Google Sheets
AND is TRUE only if every test is true. OR is TRUE if any test is true. NOT flips TRUE and FALSE. They are mostly used inside IF.
What these functions do
A single comparison such as A2>0 answers one question. Real rules usually need several: "between 0 and 10", "Paid or Trial". AND, OR and NOT combine tests into one TRUE or FALSE, which IF can then act on. For more on that, see IF with multiple conditions.
Syntax
=AND(logical_expression1, [logical_expression2, ...])
=OR(logical_expression1, [logical_expression2, ...])
=NOT(logical_expression)
Per Google's help page, AND returns TRUE only when all supplied arguments are logically true, and FALSE if any is false. OR returns TRUE if any argument is true, and FALSE only if all are false. NOT reverses a logical value, so NOT(TRUE) returns FALSE. The page also notes that the number 0 counts as false and every other number, including negatives, counts as true.
A step-by-step example
Cell A2 holds the number 5 and cell A3 holds the text Paid.
| A | |
|---|---|
| 1 | Value |
| 2 | 5 |
| 3 | Paid |
=AND(A2>0, A2<10)
This returns TRUE, because 5 is both above 0 and below 10. If either test failed, the result would be FALSE.
Use OR inside IF
=IF(OR(A3="Paid", A3="Trial"), "Active", "Inactive")
This returns Active, because A3 equals Paid, and one true test is enough for OR.
Common mistakes
- Mixing up AND and OR. AND needs every test to pass, OR needs only one.
- Writing a chained comparison. Use
AND(A2>0, A2<10)rather than0<A2<10. - Forgetting that any nonzero number is true.
Try it yourself
Use the cells above (A2 and A3). Type your formula into the box, starting with =, then press Check.
Exercise 1: Between 0 and 10
Write a formula that is TRUE only if A2 is greater than 0 and less than 10.
Exercise 2: Paid or Trial
Write a formula that shows Active if A3 is "Paid" or "Trial", and Inactive otherwise.
What to learn next
- IF: run a test and return one of two results.
- IFS: test several conditions in order without nesting.
- ISTEXT: check whether a value is text.
Syntax and rules in this guide were checked against Google's AND/OR/NOT help page. See all guides.