SheetHelper logo
excelif functionandorformulas2026-07-27

IF with AND OR in Excel

Use IF with AND when every condition must be true. Use IF with OR when any one condition can be true.

Quick examples:

=IF(AND(B2="East",D2="Paid"),"Include","Skip")
=IF(OR(C2="Pro",E2>=500),"Review","Standard")

The first formula includes a row only when Region is East and Status is Paid. The second formula flags a row when Product is Pro or Amount is at least 500.

If you need a broader IF overview first, start with the Excel IF Function Tutorial. This guide focuses on multiple-condition logic with AND, OR, and combined rules.

1. IF, AND, and OR: Quick Difference

Each function has a different job inside the formula.

Function What It Does Example Question
IF Returns one result when a test is true and another when false Should this row be approved?
AND Checks whether all conditions are true Is it East and Paid?
OR Checks whether at least one condition is true Is it East or West?

The most common patterns are:

Need Formula Pattern
Return a label when two rules are both true =IF(AND(condition1,condition2),true_result,false_result)
Return a label when either rule is true =IF(OR(condition1,condition2),true_result,false_result)
Combine required and optional logic =IF(AND(required,OR(option1,option2)),true_result,false_result)

Think of AND and OR as the test inside IF. They decide whether the logical test is true; IF decides what value to return.

2. Example Data

Suppose you have this order list:

Order ID Region Product Status Amount
O-101 East Basic Paid 420
O-102 West Pro Pending 680
O-103 East Pro Paid 750
O-104 North Basic Paid 310
O-105 East Pro Pending 920
O-106 West Basic Paid 510

We will use this table for the examples below.

3. Use IF with AND When All Conditions Must Match

Use AND when every rule must be true.

=IF(AND(B2="East",D2="Paid"),"Include","Skip")

Result for the sample data:

Order ID Region Status Result
O-101 East Paid Include
O-102 West Pending Skip
O-103 East Paid Include
O-104 North Paid Skip

How it works:

Formula Part Meaning
B2="East" First condition
D2="Paid" Second condition
AND(...) True only when both conditions are true
"Include" Result when both conditions pass
"Skip" Result when at least one condition fails

Use this pattern for approval rules, eligibility checks, quality checks, and status labels.

4. Use IF with OR When Any Condition Can Match

Use OR when one matching condition is enough.

=IF(OR(C2="Pro",E2>=500),"Review","Standard")

Result:

Order ID Product Amount Result
O-101 Basic 420 Standard
O-102 Pro 680 Review
O-103 Pro 750 Review
O-104 Basic 310 Standard
O-106 Basic 510 Review

This formula returns Review if Product is Pro or Amount is at least 500. If neither condition is true, it returns Standard.

Use OR for exception lists, review flags, flexible eligibility rules, and cases where several different triggers should produce the same result.

5. Combine AND and OR in One IF Formula

Many real rules need both required conditions and optional conditions.

Example: flag paid orders from East or West.

=IF(AND(D2="Paid",OR(B2="East",B2="West")),"Target","Other")

This means:

Logic Requirement
D2="Paid" The order must be paid
OR(B2="East",B2="West") The region can be East or West
AND(...) Both the paid rule and the region rule must be true

Result:

Order ID Region Status Result
O-101 East Paid Target
O-102 West Pending Other
O-103 East Paid Target
O-104 North Paid Other
O-106 West Paid Target

The parentheses matter. OR(B2="East",B2="West") must be grouped before it is combined with the paid-status rule.

6. Use IF with AND for Numeric Thresholds

Text checks and number checks can live in the same formula.

Example: approve paid orders of at least 500:

=IF(AND(D2="Paid",E2>=500),"Approve","Hold")

Result:

Order ID Status Amount Result
O-101 Paid 420 Hold
O-102 Pending 680 Hold
O-103 Paid 750 Approve
O-106 Paid 510 Approve

This is useful when one rule checks a label and another checks a number.

7. Use IF with OR for Exception Flags

OR is helpful when several different issues should get the same label.

Example: flag rows that are pending or high value:

=IF(OR(D2="Pending",E2>=700),"Check","OK")

Result:

Order ID Status Amount Result
O-101 Paid 420 OK
O-102 Pending 680 Check
O-103 Paid 750 Check
O-104 Paid 310 OK
O-105 Pending 920 Check

This pattern is cleaner than writing separate helper columns for each trigger when all triggers lead to the same output.

8. Return Numbers Instead of Text

IF can return numbers, percentages, or calculations.

Example: give a 10% discount to paid Pro orders:

=IF(AND(C2="Pro",D2="Paid"),E2*0.90,E2)

Result:

Order ID Product Status Amount Final Amount
O-102 Pro Pending 680 680
O-103 Pro Paid 750 675
O-105 Pro Pending 920 920

Only rows where Product is Pro and Status is Paid receive the discount.

If your goal is to add all matching values instead of returning a value per row, use SUMIF vs SUMIFS in Excel.

9. IF with AND OR vs COUNTIFS, SUMIFS, and FILTER

Use the function that matches the output you need.

Need Better Formula
Return a label for each row IF with AND or OR
Count matching rows COUNTIF or COUNTIFS
Add matching amounts SUMIF or SUMIFS
Return the matching rows FILTER

Examples:

Goal Formula Type
Mark paid East orders as Include IF with AND
Count paid East orders COUNTIFS
Total paid East sales SUMIFS
Show paid East rows in a separate list FILTER

For the count version, see COUNTIF vs COUNTIFS in Excel. For extracted rows, see the Excel FILTER function tutorial.

10. Common IF with AND OR Mistakes

Mistake 1: Putting AND or OR Outside IF Incorrectly

This returns only TRUE or FALSE:

=AND(B2="East",D2="Paid")

That is useful for testing, but it does not return a custom label. Wrap it in IF when you need labels:

=IF(AND(B2="East",D2="Paid"),"Include","Skip")

Mistake 2: Forgetting Quotation Marks Around Text

This is wrong:

=IF(AND(B2=East,D2=Paid),"Include","Skip")

Text criteria need quotation marks:

=IF(AND(B2="East",D2="Paid"),"Include","Skip")

Mistake 3: Using AND When You Need OR

This formula can never be true if one cell can contain only one region:

=IF(AND(B2="East",B2="West"),"Target","Other")

Use OR instead:

=IF(OR(B2="East",B2="West"),"Target","Other")

Mistake 4: Missing Parentheses in Combined Logic

Combined AND and OR logic needs clear grouping:

=IF(AND(D2="Paid",OR(B2="East",B2="West")),"Target","Other")

When a formula gets hard to read, test the AND part and the OR part in separate helper cells first.

Mistake 5: Hidden Spaces or Inconsistent Labels

Excel compares text exactly. Paid and Paid are different because the second value has a trailing space.

If logic looks correct but results are wrong, clean labels with Excel Data Cleaning or use Find and Replace in Excel for one-time standardization.

11. Practice Example

Using the sample table from section 2, answer these questions:

Question Formula
Mark paid East orders =IF(AND(B2="East",D2="Paid"),"Include","Skip")
Mark Pro or high-value orders =IF(OR(C2="Pro",E2>=500),"Review","Standard")
Mark paid East or West orders =IF(AND(D2="Paid",OR(B2="East",B2="West")),"Target","Other")
Approve paid orders of at least 500 =IF(AND(D2="Paid",E2>=500),"Approve","Hold")

Reference answers:

Order ID Paid East? Pro or High Value? Paid East/West? Approve?
O-101 Include Standard Target Hold
O-102 Skip Review Other Hold
O-103 Include Review Target Approve
O-104 Skip Standard Other Hold
O-105 Skip Review Other Hold
O-106 Skip Review Target Approve

12. Summary

Use IF with AND when all conditions must be true. Use IF with OR when any condition can be true. Combine them when a rule has required parts plus optional choices, such as paid orders from East or West. Keep parentheses clear, put text criteria in quotation marks, and switch to COUNTIFS, SUMIFS, or FILTER when you need counts, totals, or returned rows instead of labels.

13. Related Guides

Need help fixing a formula?

Share the formula, a small sample table, and the result you expected. This free beta helps us learn which Excel problems readers actually need solved.

Ask for formula help