SheetsdrillAND and OR
AND(condition1, condition2, ...) returns TRUE only when every condition is TRUE, and OR(condition1, ...) returns TRUE when at least one is. They are mostly used inside IF, such as IF(AND(B2>1000, C2="West"), "Yes", "No"); inside FILTER and ARRAYFORMULA use multiplication for AND and addition for OR instead.
Syntax
=AND(logical_expression1, [logical_expression2, ...]) and =OR(logical_expression1, [logical_expression2, ...])| Part | What it is |
|---|---|
logical_expression | A comparison or a cell containing TRUE/FALSE. Any number of them. |
Examples
=IF(AND(B2="Open", C2="High"), "Escalate", "")Result: Escalate only for open, high-priority rows
=IF(OR(A2="Paid", A2="Refunded"), "Closed", "Pending")Result: Closed for either status
=AND(B2>=0, B2<=100)Result: TRUE when B2 is between 0 and 100 inclusive
Common mistakes
- FILTER(A2:C, AND(B2:B="Open", C2:C="High")) returns nothing because AND collapses the whole columns into one value; pass separate conditions or multiply them.
- Writing B2>0 AND B2<10 as a formula gives #NAME?; AND is a function: AND(B2>0, B2<10).
- A comparison like 0<B2<100 does not work in Sheets; split it into two conditions inside AND.
Practice AND and OR
These drills check your answer on live data, free, no account needed.
- Open high-priority ticketsCount the tickets that are both "High" priority and "Open". Answer in G1.
Questions
How do I use AND in a COUNTIF?
Use COUNTIFS, which is an AND across criteria: =COUNTIFS(B:B, "Open", C:C, "High"). For OR, add two COUNTIFs.
What does NOT do?
NOT(condition) flips TRUE and FALSE: =IF(NOT(ISBLANK(A2)), "Filled", "Empty").
Related
- IFHow the IF function in Google Sheets returns one value when a condition is true and another when it is false, with examples for text, blanks and nested IFs.
- FILTERHow the FILTER function in Google Sheets returns only rows that match one or more conditions, with examples for AND, OR, dates and text, plus common errors.
- COUNTIFSHow COUNTIFS in Google Sheets counts rows where several conditions are true, with examples for status and priority, date ranges and not-equal, plus errors.
- IFSHow the IFS function in Google Sheets checks several conditions in order and returns the first match, with examples for grades, tiers and bands, plus errors.