How to Use IF with AND and OR in Excel

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

=IF(AND(B5="Active",C5>=500),"Qualifies","Does not qualify")

=IF(OR(C5>=500,D5="Priority"),"Review","Standard")

If you need a refresher on a one-condition formula first, read How to Use the IF Function in Excel.

Choose AND or OR before you write the formula

The most important decision is the rule you want Excel to test:

Example data: qualify an order

Suppose each order has a status in column B, an amount in column C, and a customer type in column D. The table uses four rows so you can see how the same inputs behave under different rules.

Use IF with AND when every condition is required

To qualify an order only when it is Active and its amount is at least $500, enter this formula in the first result cell:

=IF(AND(B5="Active",C5>=500),"Qualifies","Does not qualify")

AND(B5="Active",C5>=500) returns TRUE only when both tests are true. That is why order 104 qualifies, while order 105 does not: it is Active, but its amount is only $250.

The >= operator includes the threshold. An Active order with an amount exactly equal to $500 also qualifies.

Use IF with OR when either condition is enough

Now use a different rule: mark an order for review when its amount is at least $500 or the customer is Priority.

=IF(OR(C5>=500,D5="Priority"),"Review","Standard")

OR returns TRUE as soon as one of its tests is true. Order 105 is marked Review even though its amount is below $500, because its customer type is Priority. Order 107 is Standard because neither condition is true.

Combine AND and OR in the same IF formula

Sometimes a rule needs one required condition plus a choice between two others. For example, an order qualifies only when it is Active and it either meets the amount threshold or belongs to a Priority customer:

=IF(AND(B5="Active",OR(C5>=500,D5="Priority")),"Qualifies","Does not qualify")

Read the formula from the inside out:

1. OR(C5>=500,D5="Priority") checks whether the amount is at least $500 or the customer is Priority.
2. AND(B5="Active", ...) then checks that the order is Active as well.
3. The outer IF returns Qualifies or Does not qualify.

Order 105 qualifies under this mixed rule because it is Active and Priority. Order 106 does not qualify even though its amount is $750, because its status is Inactive.

If your goal is to add Sales, Hours, or another numeric value from many matching rows rather than return one result for each row, use How to Use the SUMIFS Function in Excel with Multiple Criteria.

Common IF, AND, and OR mistakes

Using AND when you mean OR

Use AND for “this and that.” Use OR for “this or that.” If an order should be reviewed when either trigger occurs, AND is too strict because it requires both triggers.

Forgetting quotation marks around text

Text typed directly into a formula needs quotation marks: use "Active" and "Priority". Cell references and numbers do not need quotation marks.

Missing a parenthesis in a mixed formula

With a combined formula, close the inner OR(...) before closing AND(...), then close IF(...). If the formula returns an error, check the parentheses first. For a broader guide to formula-error codes and safe first fixes, read Excel Formula Errors Explained.

Using nested IF for one combined true-or-false decision

Use AND or OR when you need one result based on combined tests. A nested IF is more appropriate when several different conditions need several different outputs, such as letter grades. Long nested formulas can become difficult to maintain.

Source and further reading

Microsoft documents the current IF with AND, OR, and NOT patterns, including their syntax and examples. For guidance on when a growing formula should become a different solution, see Microsoft’s nested IF guidance.