Single-condition IF formulas only get you so far. Learn how to combine IF with AND, OR, and NOT to build multi-condition decision logic that mirrors real business rules — from compliance flags to tiered eligibility classifiers. Practical formulas, real datasets, and a hands-on project included.
You're reviewing a sales report and your manager wants to flag reps who hit their quota and maintained a customer satisfaction score above 80%. Or maybe you need to categorize transactions as "Needs Review" if the amount exceeds $10,000 or if the vendor is flagged in your system. These are real, everyday decisions — and they all share one thing in common: they can't be answered with a single condition.
A standalone IF formula can handle one decision point. But real business logic almost always involves multiple conditions firing together, or multiple conditions where any one of them can trigger an outcome. This is where IF combined with AND and OR transforms from a simple formula into a decision engine that mirrors how you actually think about data. By the end of this lesson, you'll be building formulas that evaluate two, three, or more conditions simultaneously, handling mixed AND/OR logic, and structuring rules that your colleagues can read and maintain.
What you'll learn:
This lesson assumes you're comfortable with IF on its own — writing basic =IF(condition, value_if_true, value_if_false) formulas without hand-holding. You should also understand how cell references work, including absolute and mixed references, since multi-condition formulas almost always involve locking down reference ranges. If you've completed the Essential Excel Functions lesson covering SUM, AVERAGE, COUNT, IF, and COUNTIF, you're ready.
Before you nest AND or OR inside IF, you need to understand what they actually return on their own. This is the step most tutorials skip, and it's why people get confused when their formulas don't behave as expected.
Both AND and OR are logical functions — they evaluate one or more conditions and return either TRUE or FALSE. That's it. They don't return text, they don't return numbers by default. Just TRUE or FALSE.
=AND(2+2=4, 10>5) → TRUE (both conditions are true)
=AND(2+2=4, 10<5) → FALSE (second condition fails)
=OR(2+2=5, 10>5) → TRUE (second condition is true, so OR passes)
=OR(2+2=5, 10<5) → FALSE (both conditions fail)
AND requires every condition to be true. If even one is false, the whole thing returns FALSE.
OR requires at least one condition to be true. It only returns FALSE when every condition fails.
Here's why this matters: IF's first argument — the logical test — expects something that resolves to TRUE or FALSE. That's exactly what AND and OR produce. So when you write =IF(AND(...), ...), you're feeding IF a TRUE/FALSE value, same as if you'd written =IF(A1>100, ...). The structure is consistent; you're just letting AND or OR do the condition-evaluation work before IF makes its decision.
Key insight
AND and OR are TRUE/FALSE factories. IF is a decision-maker that acts on TRUE/FALSE inputs. Keep these roles distinct in your head and multi-condition formulas become much less intimidating.
You can test this in a blank worksheet: type a few values and write standalone AND and OR formulas to see them return TRUE or FALSE before you even involve IF. It's a 60-second exercise that builds genuine intuition.
The most common use case in business data work: an action triggers only when multiple criteria are all satisfied simultaneously. Commission bonuses that require both revenue and margin targets. Access approvals that require employment status and department clearance. Quality flags that require a defect count and a failure rate threshold.
The syntax:
=IF(AND(condition1, condition2, ...), value_if_true, value_if_false)
Let's work with a realistic dataset. Imagine you're managing a sales team performance review. Your spreadsheet has:
Your rule: A rep qualifies for a performance bonus if their revenue meets or exceeds quota and their satisfaction score is 85 or higher.
=IF(AND(B2>=C2, D2>=85), "Bonus Eligible", "Not Eligible")
Walk through what happens: Excel first evaluates B2>=C2 — did this rep hit quota? Then it evaluates D2>=85 — did they hit the satisfaction threshold? AND combines both results. If both are TRUE, AND returns TRUE, and IF returns "Bonus Eligible." If either one fails, AND returns FALSE, and IF returns "Not Eligible."
You can extend AND to three, four, or more conditions with no extra syntax complexity:
=IF(AND(B2>=C2, D2>=85, E2="Active"), "Bonus Eligible", "Not Eligible")
Here, E2="Active" adds an employment status check. All three must be true.
Tip
AND can technically hold up to 255 conditions. Practically, once you're beyond five or six, your formula becomes hard to audit and maintain. If you find yourself writing AND(cond1, cond2, cond3, cond4, cond5, cond6...), consider whether a helper column breaking out some conditions would make the workbook more readable and debuggable.
One thing that trips people up: comparison operators inside AND. Make sure each condition inside AND is a complete logical expression — it needs to resolve to TRUE or FALSE on its own. AND(B2, C2) doesn't check whether B2 and C2 are equal to anything; it checks whether they're non-zero/non-empty (truthy). That's probably not what you want. Write explicit comparisons: AND(B2>=0, C2>0).
OR-based logic handles the "flag it if anything looks wrong" family of business rules. Compliance teams use it constantly — a transaction gets flagged for review if the amount is over a threshold or the counterparty is on a watchlist or the transaction type is unusual.
The syntax mirrors IF/AND exactly:
=IF(OR(condition1, condition2, ...), value_if_true, value_if_false)
Practical example: You're working with an accounts payable dataset. Flag any invoice for manual review if:
=IF(OR(B2>25000, D2="Unverified", E2="Net 90"), "Flag for Review", "Approved")
The moment any one of those three conditions is true, OR returns TRUE, and IF returns "Flag for Review." All three have to be false simultaneously for the invoice to pass as "Approved."
This is fundamentally different from the AND version — with OR, you're being permissive about which condition triggers the outcome. With AND, you're being strict about requiring all conditions.
Warning
OR logic can be deceptively broad. If you're building a compliance rule using OR and you're not careful about your conditions, you can end up flagging 70% of your dataset. Always sanity-check OR-based formulas against a sample of your data before deploying them. Count how many rows trigger the flag and make sure that proportion makes business sense.
Here's another common OR pattern — categorizing things by membership in a set of values:
=IF(OR(A2="Manager", A2="Director", A2="VP"), "Leadership", "Individual Contributor")
This is cleaner than a long nested IF and much more readable. You're essentially asking: "Is this job title any one of these three things?"
Now we get to the formulas that actually require some thought. Real business rules are rarely pure AND or pure OR — they're a mix. Something like: "Flag this transaction if it's over $10,000 AND either the vendor is unverified OR the department is Facilities."
The key to writing mixed AND/OR logic is understanding operator precedence — which logical operation wraps around the other. In Excel, AND and OR don't have different precedence levels (unlike arithmetic where multiplication beats addition). You control precedence entirely by which function is the outer wrapper.
Let's break down three common patterns:
Business rule: Award "Priority Customer" status if the customer has been with you for 5+ years AND either their annual spend is over $50,000 OR they have an enterprise contract.
=IF(AND(B2>=5, OR(C2>50000, D2="Enterprise")), "Priority", "Standard")
Execution order:
This reads as: "Must have 5+ years AND must satisfy at least one of the spend/contract criteria."
Business rule: Flag a loan application for secondary review if the applicant has a credit score under 650 OR if they have both a debt-to-income ratio over 45% AND less than 2 years at their current employer.
=IF(OR(B2<650, AND(C2>0.45, D2<2)), "Secondary Review", "Standard Processing")
Execution order:
This reads as: "Either the credit score alone is a problem, OR the combination of DTI ratio and employment history is a problem."
This is where many real compliance and classification rules live. Let's say you're categorizing employees for a training program. An employee should be enrolled if:
=IF(AND(OR(B2="Sales", B2="Marketing"), OR(C2<1, D2<3)), "Enroll", "Skip")
Both OR conditions are inside AND — both OR blocks must resolve to TRUE for the employee to be enrolled. This is a genuinely complex rule rendered in one clean formula.
Key insight
The outer function determines the final logic gate. AND(OR(), ...) means AND wins — everything inside must be satisfied. OR(AND(), ...) means OR wins — any branch being satisfied is enough. Build your mental model from the outside in.
You'll occasionally need to invert a condition — do something when a condition is not true. NOT is the third member of this logical family, and it integrates cleanly with AND and OR.
NOT simply flips TRUE to FALSE and FALSE to TRUE:
=NOT(A2="Cancelled") → TRUE when A2 is anything except "Cancelled"
Practical use: You want to calculate a retention bonus, but only for employees who are NOT on a performance improvement plan AND have been with the company over 3 years.
=IF(AND(NOT(C2="PIP"), B2>3), "Retention Bonus Eligible", "Not Eligible")
You could also write C2<>"PIP" using the not-equal-to operator, and in simple cases that's preferable. But NOT becomes especially useful when wrapping more complex expressions:
=IF(NOT(OR(B2="Terminated", B2="On Leave", B2="Suspended")), "Active", "Inactive")
This reads as: "If it's NOT the case that any of these statuses apply, classify as Active." Flipping the OR with NOT is often more readable than writing out a long AND chain of <> comparisons.
For multi-tier outcomes — not just true/false, but Bronze/Silver/Gold, Low/Medium/High/Critical — you'll combine nested IFs with AND/OR conditions. If you've worked through nested IF and IFS functions for complex business rules, you know the basic nesting pattern. Here we're extending it with multi-condition gates at each tier.
Scenario: You're building a risk rating system for loan applications. The tiers are:
=IF(OR(B2<600, C2>0.5),
"High Risk",
IF(AND(B2>=600, B2<=699, C2>=0.36, C2<=0.5),
"Medium Risk",
"Low Risk"))
The formula evaluates High Risk first — if either disqualifying condition is true, it stops there. Then it checks Medium Risk with a full AND condition spanning both columns. Anything that fails both becomes Low Risk by default.
Tip
When building tiered IF formulas, always order your conditions from most restrictive/urgent to least. Put your "hard stop" conditions first. This makes the formula more efficient (Excel stops evaluating as soon as it finds a TRUE) and makes your business logic easier to audit — because the formula reads in the same order that a human reviewer would work through the rules.
A more complex version for a customer discount tier system:
=IF(AND(B2="Gold", C2>=10000),
"15% Discount",
IF(OR(B2="Gold", AND(B2="Silver", C2>=5000)),
"10% Discount",
IF(AND(B2="Silver", C2>=2500),
"5% Discount",
"No Discount")))
This handles four distinct outcomes with mixed AND/OR logic at different tiers. It's complex, but it's also the kind of formula you'd actually find in a real pricing model.
Standard AND and OR only evaluate individual cells against conditions. When you need to check whether any value in a range meets a condition, or whether all values in a range meet a condition, you need a different approach.
For checking if all values in a range meet a condition (AND-style across a range):
=IF(AND(B2:B10>0), "All Positive", "Some Negative or Zero")
Warning
In older Excel versions (pre-Microsoft 365 dynamic arrays), AND with a range like this doesn't work as you'd expect — it only evaluates the first cell. In Microsoft 365, it works correctly. If you're in an older environment, use SUMPRODUCT or COUNTIF for range-based logical checks. For example: =IF(COUNTIF(B2:B10,"<=0")=0, "All Positive", "Some Negative or Zero") works in any version.
For checking if any value in a range meets a condition (OR-style across a range):
=IF(COUNTIF(B2:B10,"<0")>0, "Contains Negative Values", "All Non-Negative")
COUNTIF counts how many cells match the condition. If that count is greater than zero, at least one cell met the condition — functionally identical to OR across a range. If you need this for multi-condition range checks, SUMIFS, COUNTIFS, and AVERAGEIFS handle multi-criteria counting elegantly.
Let's put everything together into a meaningful, complete example. You're building a benefits eligibility classifier for HR. The dataset has:
The business rules:
Premium Benefits: Full-Time AND employed 12+ months AND hours >= 40 Standard Benefits: (Full-Time AND employed 6–11 months) OR (Part-Time AND hours >= 30 AND employed 12+ months) Basic Benefits: Part-Time AND employed 3+ months AND hours >= 20 Not Eligible: Everyone else (Contractors, too new, too few hours)
Here's the formula, built progressively:
=IF(AND(B2="Full-Time", C2>=12, D2>=40),
"Premium Benefits",
IF(OR(AND(B2="Full-Time", C2>=6, C2<12),
AND(B2="Part-Time", D2>=30, C2>=12)),
"Standard Benefits",
IF(AND(B2="Part-Time", C2>=3, D2>=20),
"Basic Benefits",
"Not Eligible")))
Take a moment to trace this for a few employee types:
Note
For a real HR workbook, you'd want to store these rules in a separate reference table and use lookup-based approaches for easier rule maintenance. But this formula structure directly encodes business logic and is completely auditable by a non-technical reviewer who reads it line by line.
To make this formula maintainable, consider using named ranges for your thresholds — replace the hard-coded values like 12, 40, 30, and 20 with named constants. When HR changes the rules (and they will), you update one cell instead of hunting through every formula.
Multi-condition logic isn't just for formula columns. Conditional formatting accepts the same AND and OR logic, letting you visually flag rows based on complex conditions.
To apply conditional formatting using a multi-condition formula:
For example, to highlight rows where an employee qualifies for Premium Benefits:
=AND($B2="Full-Time", $C2>=12, $D2>=40)
Notice the mixed references — $B2 locks the column but lets the row float. This is essential for row-based conditional formatting rules. When Excel applies this rule to each row, it evaluates the formula using that row's data while always checking columns B, C, and D specifically.
Tip
When building conditional formatting rules with complex AND/OR logic, test your formula in a regular cell first. Confirm it returns TRUE and FALSE exactly as expected before moving it into the conditional formatting dialog. The formatting dialog gives you no error feedback if your formula is wrong — it just silently doesn't format anything.
Problem: =IF(A2>100, AND(B2="Yes"), "Flag", "OK") — AND is in the wrong argument position.
Fix: AND must be in the logical test position: =IF(AND(A2>100, B2="Yes"), "Flag", "OK")
Problem: =IF(AND(B2=Full-Time, C2>=12), ...) — Excel interprets Full-Time as a subtraction formula.
Fix: Always wrap text in double quotes: =IF(AND(B2="Full-Time", C2>=12), ...)
Problem: =IF(AND(B2:B10>0), ...) in a non-Microsoft-365 environment — only B2 gets evaluated.
Fix: Use =IF(COUNTIF(B2:B10,"<=0")=0, ...) for cross-version compatibility.
Symptom: Your formula returns "Flag" for rows you expected to pass.
Diagnostic: Break the OR out of the formula and test each condition separately in helper columns. Identify which condition is unexpectedly true for those rows.
Helper column 1: =B2>25000
Helper column 2: =D2="Unverified"
Helper column 3: =E2="Net 90"
Once you can see each condition's TRUE/FALSE output row by row, the culprit becomes obvious. This is also a core technique from Excel formula auditing.
Symptom: You've written =IF(AND(OR(...), ...), IF(OR(AND(...), ...), IF(...))) and even you can't remember what it does two days later.
Fix options:
Problem: =IF(AND(A2="1", B2=1), ...) — if A2 contains the number 1 (not text), A2="1" returns FALSE even though A2 looks like it contains 1.
Fix: Match your comparison type to your data type. Use =AND(A2=1, B2=1) for numbers, =AND(A2="text", ...) for text. Use ISNUMBER() or ISTEXT() from the IS functions family to check what type you're actually working with.
IF/AND/OR is powerful, but it's not always the right tool. Know when to reach for something else:
Use IFS instead of nested IF when you have more than 3–4 non-overlapping tiers with no mixed AND/OR logic. IFS reads much more cleanly for sequential condition chains.
Use SWITCH when you're categorizing based on exact value matches (not ranges or inequalities). SWITCH(A2, "Sales", "Revenue", "HR", "Headcount", "IT", "Infrastructure", "Other") is far cleaner than a chain of OR-based IFs.
Use SUMPRODUCT when you need to count or sum across rows based on multi-condition logic without a helper column. SUMPRODUCT natively supports AND logic (via multiplication) and OR logic (via addition), and handles ranges that IF/AND/OR can't.
Use FILTER (Microsoft 365) when you need to extract matching rows rather than label them. =FILTER(range, AND_condition_array) returns the actual data. See Dynamic Arrays: FILTER, SORT, and UNIQUE Explained for the full picture.
Use helper columns when your logic is genuinely complex and you need it to be auditable by someone else (or by future-you). Pre-computing IsHighValueClient, IsBelowThreshold, and IsActiveAccount as separate TRUE/FALSE columns, then combining them in a final column, is often more professional and maintainable than a single impenetrable formula.
Build this in a fresh worksheet:
Scenario: You're a data analyst at a software company reviewing customer renewal risk. You have 10 fictional customer rows with these columns:
| A: Customer | B: Plan | C: Monthly Logins | D: Support Tickets | E: Contract Value |
|---|---|---|---|---|
| Acme Corp | Enterprise | 45 | 2 | 85000 |
| BetaCo | Starter | 3 | 8 | 1200 |
| Gamma Ltd | Professional | 22 | 1 | 18000 |
| Delta Inc | Enterprise | 12 | 5 | 92000 |
| Epsilon LLC | Starter | 1 | 12 | 800 |
| Zeta GmbH | Professional | 30 | 0 | 15000 |
| Eta Corp | Enterprise | 55 | 3 | 110000 |
| Theta Inc | Starter | 8 | 4 | 1500 |
| Iota Ltd | Professional | 18 | 7 | 22000 |
| Kappa Co | Enterprise | 6 | 11 | 78000 |
Your tasks:
In column F, write a formula that flags "High Churn Risk" if the customer has fewer than 10 monthly logins AND more than 5 support tickets. Otherwise return "Normal."
In column G, write a formula that flags "Account Manager Alert" if the customer is on the Enterprise plan OR their contract value exceeds $50,000. Otherwise return "Standard."
In column H, write a tiered renewal risk score:
Add conditional formatting to column H that highlights "Critical" cells in red and "At Risk" cells in orange using formula-based rules.
Expected answers for column F: BetaCo (High Churn Risk), Epsilon LLC (High Churn Risk), Kappa Co (High Churn Risk) should trigger. Check your logic against each row manually before trusting the formula.
You've now moved from single-condition IF formulas into genuine multi-condition decision logic. Here's what to carry forward:
<> comparisons when wrapping complex expressions.The natural next step is exploring more sophisticated multi-criteria calculations. When you need to count or sum rows based on the same kind of multi-condition logic you've been building here, SUMIFS, COUNTIFS, and AVERAGEIFS are your tools — they handle AND-condition counting and aggregation natively across entire datasets without IF formulas in every row. If your decision logic is branching into more than four or five tiers, Nested IF, IFS, and SWITCH Functions will show you how to restructure complex rules into cleaner, more maintainable patterns.