A single IF statement tests one condition. Many real-world tasks, however, require several conditions at once — approve a discount only if the customer is VIP and the order is above £500, or flag a shipment if it is late or damaged. The AND function returns TRUE only when every condition is met. The OR function returns TRUE when at least one condition is met.
By nesting AND or OR inside IF, you gain precise control over multi-condition decisions. Consequently, formulas that previously needed several nested IFs become a single, readable line.
What Is the Syntax for AND and OR?
Both functions share the same repeating pattern — a list of conditions separated by commas.
| Argument | Required? | What it does |
|---|---|---|
| logical1 | Required | The first condition to evaluate. This must be an expression that returns TRUE or FALSE — for example, A2>100 or B2="Active". |
| logical2… | Optional | Additional conditions. Up to 255 are allowed. Each is evaluated independently, and the results are combined according to the AND or OR rule. |
How Do AND and OR Evaluate Conditions?
The key difference between the two functions is how they combine TRUE and FALSE results. AND is strict — one FALSE brings the whole result down to FALSE. OR is lenient — one TRUE is enough to produce TRUE.
AND Truth Table
| Condition 1 | Condition 2 | AND result |
|---|---|---|
| TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE |
| FALSE | TRUE | FALSE |
| FALSE | FALSE | FALSE |
OR Truth Table
| Condition 1 | Condition 2 | OR result |
|---|---|---|
| TRUE | TRUE | TRUE |
| TRUE | FALSE | TRUE |
| FALSE | TRUE | TRUE |
| FALSE | FALSE | FALSE |
Example 1: IF with AND — All Conditions Must Pass
Use IF(AND(...)) when every condition must be TRUE before the formula takes action. For instance, a sales bonus should only apply if the rep has hit both a revenue target and an account target. Meeting just one of the two is not enough.
Example 2: IF with OR — Any Condition Triggers the Result
Use IF(OR(...)) when any one condition being TRUE is enough to trigger a result. This is ideal for exception flagging, eligibility checks, and alert systems — where several different situations all lead to the same action.
Example 3: Combine AND and OR in One Formula
Real scenarios often need both functions together. For instance, a discount might apply if the product is in a specific category AND (the customer is a VIP OR the order quantity is above 50). Nesting AND inside OR, or vice versa, handles these combined conditions cleanly.
How to Structure Nested AND and OR
Place the stricter gate — the AND — as the outer function, and put the alternatives — the OR — inside it. Alternatively, think of it in plain English first, then translate. Writing the logic in words before typing the formula prevents bracket errors and saves time.
Example 4: AND and OR Without IF — Standalone Use
AND and OR do not have to live inside IF. Both functions return TRUE or FALSE directly, which makes them useful on their own. Specifically, you can use the standalone result in conditional formatting rules, data validation, or as an input to SUMPRODUCT and other array-aware functions.
Example 5: The NOT Function — Invert a Condition
NOT is the third member of the logical family. It simply reverses TRUE to FALSE and FALSE to TRUE. This is useful when it is easier to define what you do not want than what you do. Additionally, NOT pairs naturally with AND and OR to express exclusion logic.
Example 6: Multi-Level Eligibility Check
A common real-world challenge is determining eligibility across several tiers. For example, premium support might be available to customers who are both on an Enterprise plan and have been active for over 12 months — OR to any customer who has spent over £10,000 regardless of their plan. Nesting AND and OR makes this kind of tiered logic straightforward to express and audit.
How to Fix Common AND and OR Errors
#VALUE! error in AND or OR
This error appears when a condition inside AND or OR evaluates to something that is neither TRUE nor FALSE — for example, a text string or an empty cell used in a numeric comparison. Check each condition individually by typing it into a spare cell. If it returns an error, fix that condition before rebuilding the full formula.
Formula returns the wrong result
Often this is a logic order issue. AND and OR do not short-circuit in the same way as some programming languages — Excel evaluates all conditions. Also check operator precedence: AND evaluates before OR when both appear at the same nesting level. Consequently, use parentheses to make your grouping explicit.
Text comparisons fail despite matching values
IF, AND, and OR are not case-sensitive — "Active" and "ACTIVE" match equally. However, trailing spaces cause silent failures. A cell containing "Active " (with a trailing space) does not match "Active". Wrap the cell reference in TRIM to clean it before comparing: =AND(TRIM(B2)="Active", C2>0).
=AND(A2:A10>0) does not check each cell. In most Excel versions, it evaluates only the first cell. To check all cells in a range, use SUMPRODUCT: =SUMPRODUCT((A2:A10>0)*1)=9 confirms all nine cells are positive. In Excel 365, array-entered AND and OR work differently — but SUMPRODUCT remains the most compatible approach.
Frequently Asked Questions
-
What is the difference between AND and OR in Excel?AND returns TRUE only when every condition is TRUE. If even one condition is FALSE, AND returns FALSE. OR, by contrast, returns TRUE as soon as any single condition is TRUE. OR only returns FALSE when every condition is FALSE. Use AND when all criteria must pass simultaneously — for example, approving a loan. Use OR when any qualifying condition is sufficient — for example, flagging an issue.
-
How do I use AND or OR inside an IF statement?Place AND or OR as the first argument of IF — the logical test: =IF(AND(condition1, condition2), value_if_true, value_if_false). For OR, replace AND with OR. Excel evaluates the inner function first, gets TRUE or FALSE, and then uses that result to decide which value to return. You can also nest AND inside OR, or OR inside AND, to express more complex rules.
-
Can I use AND and OR in SUMPRODUCT?Yes, but the syntax is different. In SUMPRODUCT, AND logic uses multiplication (*) between conditions, and OR logic uses addition (+). For example, =SUMPRODUCT((A2:A100="East")*(B2:B100>500)*C2:C100) sums column C where both conditions are met (AND). Addition handles OR: =SUMPRODUCT(((A2:A100="East")+(A2:A100="North")>0)*C2:C100). This approach works in all Excel versions without Ctrl+Shift+Enter.
More Questions About Logical Functions
-
How many conditions can AND and OR handle?Both AND and OR accept up to 255 arguments in Excel 2007 and later. In practice, however, formulas with more than five or six conditions become difficult to read and maintain. For large lists of conditions, consider alternatives — for instance, IFS for sequential tests, SWITCH for exact matches, or COUNTIF for checking whether a value appears in a list. These options are often more readable for complex rules.
-
What does the NOT function do?NOT reverses a logical value — TRUE becomes FALSE and FALSE becomes TRUE. It takes exactly one argument: =NOT(logical). For example, =NOT(A2="Complete") returns TRUE when A2 does NOT contain "Complete". NOT is especially useful in conditional formatting rules where it is clearer to state what you want to exclude rather than what you want to include. It also combines naturally with AND and OR for complex exclusion logic.
-
Which Excel versions support AND, OR, and NOT?All three functions are available in every Excel version from Excel 2000 onwards, including Microsoft 365, Excel 2024, 2021, 2019, 2016, 2013, 2010, 2007, and earlier versions. They also work in Excel for the web and Google Sheets. No special version, add-in, or setting is needed to use them.