Use AND and OR Together in One IF Formula

You can use IF with AND and OR together by putting one function inside the other. AND(test1, OR(test2, test3)) means "test1 must be true, plus either test2 or test3". OR(test1, AND(test2, test3)) means "pass on test1 alone, or on test2 and test3 together". This guide is for anyone whose rules mix "and" with "or", such as bonus, discount or approval checks, and it builds both patterns on one small table.

How AND and OR work together#

AND returns TRUE only when every test inside it is true. OR returns TRUE when at least one test is true. When you nest one inside the other, Excel works out the inner function first, and its TRUE or FALSE becomes a single test for the outer function. IF then turns that final TRUE or FALSE into the result you want.

All examples use this sales table in A1:D7:

If AND and OR are new to you, start with IF with AND and IF with OR separately, then come back here.

How to put OR inside AND#

Put OR inside AND when one rule is fixed and another rule has a choice of values. The rule: a bonus needs sales of 50,000 or more, and the region must be North or South. In E2, type:

=IF(AND(C2>=50000,OR(B2="North",B2="South")),"Bonus","No")

Here is how Excel reads row 2:

1. C2>=50000: 62,000 is at least 50,000, so TRUE.
2. OR(B2="North",B2="South"): the region is North, so TRUE.
3. AND(TRUE,TRUE) is TRUE, so IF returns Bonus.

Fill down to E7 (Windows: Ctrl+D; Mac: ⌘D). Only Aisha and Femi get Bonus. Carla has high sales but is in East, so OR fails. Ben is in South but sold under 50,000, so the sales test fails.

When OR checks one cell against several text values, there's a shorter form: OR(B2={"North","South"}). It works in every version and gives the same result.

How to put AND inside OR#

Put AND inside OR when there are two or more separate ways to pass, and at least one of them has two parts. The rule: flag for senior review if sales are 60,000 or more, or if the person has 5 or more years and at least 45,000 in sales.

=IF(OR(C2>=60000,AND(D2>=5,C2>=45000)),"Review","")

Aisha (62,000) and Carla (71,000) pass on sales alone. Ben passes the second way: 6 years and 48,000. Dmitri has 8 years but only 39,000, so his AND is FALSE, and he is not flagged. The result is Review for 3 people.

Use "" as the last argument when you want the cell to look empty if the test fails.

How to check the brackets and the logic#

The safest way to write these formulas is to read them out loud, saying "and" or "or" for each function. The bonus formula reads: "sales at least 50,000 and (North or South)". If that sentence matches your rule, the formula is right.

Then check the brackets:

• Each function opens and closes its own bracket: OR( … ) closes before AND continues.
• Excel colours matching brackets as you type, which helps you spot a missing one.
• Moving a bracket changes the meaning. OR(AND(C2>=50000,B2="North"),B2="South") means "50,000 and North, or any South row", so Ben would pass even with 48,000.

If a rule has many parts, split it into helper columns: one column for the region test and one for the sales test, then a final =IF(AND(F2,G2),"Bonus","No"). It's longer but much easier to check. The IF function guide explains the three IF arguments.

How to count rows that meet AND and OR conditions#

To count the people who get a bonus without a helper column, add one COUNTIFS for each OR option. COUNTIFS uses AND logic inside, so each one checks sales and one region:

=COUNTIFS(C2:C7,">=50000",B2:B7,"North")+COUNTIFS(C2:C7,">=50000",B2:B7,"South")

The result is 2 (Aisha and Femi). This works because no row can be in both regions, so nobody is counted twice.

For more complex mixes, SUMPRODUCT can multiply tests (AND) and add them (OR):

=SUMPRODUCT((C2:C7>=50000)*((B2:B7="North")+(B2:B7="South")))

This also returns 2. In this style, * acts as AND and + acts as OR.

When to use IFS or nested IF instead#

AND and OR give you one TRUE or FALSE, so IF can only return two results. When you need three or more outcomes, put each AND/OR test into IFS (Excel 2019, 2021 and Microsoft 365):

=IFS(AND(C2>=50000,OR(B2="North",B2="South")),"Bonus",C2>=50000,"Review",TRUE,"No")

Aisha and Femi get Bonus, Carla and Elena get Review (high sales, wrong region), and the rest get No. IFS checks tests in order and stops at the first TRUE, so put the strictest rule first. The guide to IF with multiple conditions compares nested IF, IFS and lookup tables. For range checks such as "between 40,000 and 60,000", see IF between two numbers.

Common mistakes with IF, AND and OR together#

These slips cause most wrong results:

1. Half-written comparisons. OR(B2="North","South") returns #VALUE!, because "South" on its own is not TRUE or FALSE. Write B2="South".
2. Brackets in the wrong place. Read the formula out loud to check which tests belong together.
3. Quotes around numbers. C2>="50000" compares a number with text, and Excel ranks any text above any number, so the test is always FALSE. Leave numbers unquoted.
4. Spaces in text data. "North " with a trailing space doesn't equal "North". Clean the column with TRIM.
5. Blank cells. An empty Sales cell counts as 0, so it fails C2>=50000 quietly. If blanks need their own result, test them first; see IF blank in Excel.

The AND function guide and OR function guide list the full rules, including how they treat empty cells.

Practise it#

Try the exercise on this page: it needs OR inside AND inside IF. Then practise AND, OR and NOT in a live spreadsheet, where you combine conditions inside IF step by step, and review the basics in the IF function lesson. Your formula is checked as soon as you press Enter.