What Do AND, OR, and NOT Do?
These three functions are the building blocks of conditional logic in Google Sheets. AND returns TRUE only when every condition is met. OR returns TRUE when at least one condition is met. NOT flips a TRUE to FALSE and vice versa. On their own they simply return TRUE or FALSE, but their real power shows up when you combine them with IF, IFS, or conditional formatting rules.
Syntax
=AND(condition1, [condition2, ...])
=OR(condition1, [condition2, ...])
=NOT(condition)
| Function | Parameters | Returns TRUE when... |
|---|---|---|
| AND | Two or more logical conditions | All conditions are TRUE |
| OR | Two or more logical conditions | At least one condition is TRUE |
| NOT | A single logical condition | The condition is FALSE |
You can nest up to 255 conditions inside AND or OR.
Basic Examples
AND — Check If a Student Passed All Subjects
| A | B | C | D (Formula) | D (Result) | |
|---|---|---|---|---|---|
| 1 | Name | Math | English | Passed Both? | Passed Both? |
| 2 | Ava | 78 | 82 | =AND(B2>=60, C2>=60) | TRUE |
| 3 | Ben | 55 | 71 | =AND(B3>=60, C3>=60) | FALSE |
Ben failed Math, so AND returns FALSE even though his English score is fine.
OR — Flag Orders That Need Attention
| A | B | C (Formula) | C (Result) | |
|---|---|---|---|---|
| 1 | Amount | Status | Flag? | Flag? |
| 2 | 4500 | Pending | =OR(A2>5000, B2="Pending") | TRUE |
| 3 | 1200 | Shipped | =OR(A3>5000, B3="Pending") | FALSE |
Row 2 is flagged because the status is "Pending," even though the amount is under 5000.
NOT — Invert a Condition
=NOT(ISBLANK(A2))
Returns TRUE if A2 contains any value. This is handy for filtering out empty rows.
Advanced Examples
Combining AND, OR, and NOT Inside IF
A common real-world pattern is checking multiple criteria before deciding on an action:
=IF(
AND(B2="Active", OR(C2>1000, D2="VIP"), NOT(E2="Blocked")),
"Send Offer",
"Skip"
)
This reads naturally: if the account is active, the customer has either spent over 1000 or is a VIP, and the account is not blocked, then send an offer. Otherwise, skip.
Using AND with ARRAYFORMULA for Bulk Checks
=ARRAYFORMULA(IF(AND(B2:B100>=60, C2:C100>=60), "Pass", "Fail"))
Note that AND inside ARRAYFORMULA can behave unexpectedly. In many cases, you need to replace AND with the multiplication operator:
=ARRAYFORMULA(IF((B2:B100>=60) * (C2:C100>=60), "Pass", "Fail"))
The * operator treats TRUE as 1 and FALSE as 0, so the product is 1 only when both conditions are TRUE. This is the standard workaround for array-compatible AND logic.
Common Mistakes
- Using AND/OR alone and expecting action. On their own, these functions only return TRUE or FALSE. To actually do something with that result, wrap them inside IF:
=IF(AND(A2>10, B2>10), "Yes", "No"). - Expecting AND to work row-by-row inside ARRAYFORMULA. AND collapses an array into a single TRUE or FALSE. Use multiplication (
*) for AND logic and addition (+) for OR logic when working with arrays. - Forgetting that NOT takes only one argument. Writing
=NOT(A2>5, B2<10)will throw an error. If you need to negate multiple conditions, use=NOT(AND(A2>5, B2<10))or=NOT(OR(A2>5, B2<10)).
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.