# How to use IF with AND and OR together in Excel

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").

**Excel:** `=IF(AND(B2="Yes",OR(C2>=$H$1,D2="Local")),"Yes","No")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Order | Member | Total | Region | Free shipping? |  | Minimum | 50 |
| O-1 | Yes | 64.5 | Remote |  |  |  |  |
| O-2 | Yes | 38 | Local |  |  |  |  |
| O-3 | No | 120 | Local |  |  |  |  |
| O-4 | Yes | 42.75 | Remote |  |  |  |  |
| O-5 | Yes | 50 | Remote |  |  |  |  |
| O-6 | No | 18 | Remote |  |  |  |  |

Formula in E2, filled to E7: `=IF(AND(B2="Yes",OR(C2>=$H$1,D2="Local")),"Yes","No")`

Result: Yes; Yes; No; No; Yes; No

## 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.

## Other ways

- `=IF((B2="Yes")*((C2>=$H$1)+(D2="Local")),"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")`: Grouped 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/if-with-and-and-or-together