Spreadsheet Practice

How to use IF with AND and OR together in Excel

Formula (Excel and Google Sheets)

=IF(AND(B2="Yes",OR(C2>=$H$1,D2="Local")),"Yes","No")

Nest OR inside AND (or the other way round) to match the rule: =IF(AND(B2="Yes",OR(C2>=$H$1,D2="Local")),"Yes","No").

Result on the example
one result per row in E2, filled down to E7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in E2, filled down to E7. Change any input and the result updates, or edit the formula to experiment.

How it works

The rule is "a member, and either a big order or a local one". OR returns TRUE when either of its tests is TRUE, and AND requires that plus membership. O-3 is local but not a member, so it doesn't qualify; O-5 is exactly on the minimum and does.

=IF(AND(B2="Yes",OR(C2>=$H$1,D2="Local")),"Yes","No")

Result"Yes"

  1. B2"Yes"

    The value in B2.

  2. B2="Yes"TRUE

    The = operator tests whether the two sides are equal.

  3. C2$64.50

    The value in C2.

  4. $H$1$50

    The value in H1.

  5. C2>=$H$1TRUE

    The >= operator tests at least.

  6. D2"Remote"

    The value in D2.

  7. D2="Local"FALSE

    The = operator tests whether the two sides are equal.

  8. OR(C2>=$H$1,D2="Local")TRUE

    OR: TRUE if any argument is true.

    • logical1C2>=$H$1TRUE
    • logical2D2="Local"FALSE
  9. AND(B2="Yes",OR(C2>=$H$1,D2="Local"))TRUE

    AND: TRUE if every argument is true.

    • logical1B2="Yes"TRUE
    • logical2OR(C2>=$H$1,D2="Local")TRUE
  10. IF(AND(B2="Yes",OR(C2>=$H$1,D2="Local")),"Yes","No")"Yes"

    IF: Returns one value if a test is true, another if false.

    • logical_testAND(B2="Yes",OR(C2>=$H$1,D2="Local"))TRUE
    • value_if_true"Yes"
    • value_if_false"No"not used

In Google Sheets

The same formula works in Google Sheets: =IF(AND(B2="Yes",OR(C2>=$H$1,D2="Local")),"Yes","No"). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=IF((B2="Yes")*((C2>=$H$1)+(D2="Local")),"Yes","No")Yes, Yes, No…The same rule in arithmetic: * works as AND and + as OR. This is the form to use inside FILTER and SUMPRODUCT.
=IF(OR(AND(B2="Yes",C2>=$H$1),D2="Local"),"Yes","No")Yes, Yes, Yes…Different resultGrouped differently, this means local orders ship free even for non-members. O-3, a local non-member, gets free shipping.

Common mistakes

  • The grouping is the rule. Write it in words first, then mirror the brackets: moving one parenthesis changes who qualifies.
  • Text tests must match the data exactly ("Yes" vs "Y"). A dropdown keeps entries consistent.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Use IF with AND and OR together

In E2, show "Yes" for members whose order is at least the minimum in H1 or whose region is Local, otherwise "No". Fill down to E7.

Questions

Can I use AND and OR inside FILTER or ARRAYFORMULA?

No, they collapse the whole range into one TRUE or FALSE. Use * and +: =FILTER(A2:A7,(B2:B7="Yes")*((C2:C7>=50)+(D2:D7="Local"))).

How deep can I nest AND and OR?

Up to 64 levels, but past two levels a helper column is easier to check.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.