AND vs OR in Excel: All Conditions or Any

The short answer to AND vs OR in Excel: AND is TRUE only when all of its conditions are true, and OR is TRUE when any of them is true. AND makes a rule stricter with every condition you add; OR makes it looser. This guide compares them on the same staff data, shows how each works inside IF and FILTER, and covers the mistakes that trip people up in job tests.

AND vs OR at a glance#

Both functions test conditions and return TRUE or FALSE. The difference is how many conditions must pass.

A condition, also called a logical test, is any expression that gives TRUE or FALSE, such as B2>=50000 or D2="North".

How AND and OR decide: the truth table#

With two conditions, there are only four combinations. This table shows what each function returns:

AND and OR agree only when both conditions are the same. When one is TRUE and the other FALSE, AND says FALSE and OR says TRUE. That middle part of the table is where choosing the wrong function changes your answer. A few details matter when conditions point at cells rather than comparisons:

• Numbers count as logical values. 0 is FALSE and any other number is TRUE, so =AND(1,5) is TRUE and =OR(0,0) is FALSE.
• Empty cells and text in a referenced range are ignored. =AND(E2:E5) only looks at the TRUE and FALSE values in that range. If the range holds no logical values at all, the result is #VALUE!.
• Errors spread. If any condition returns an error, such as #N/A from a lookup, AND and OR return that error too, even when another condition would already decide the answer.

The AND function guide and the OR function guide list the full rules.

The same data, tested both ways#

Here is a list of sales staff:

Rule 1 with AND: sales of 50,000 or more and a rating of 4 or more#

=AND(B2>=50000,C2>=4)

Filled down, this returns TRUE only for Amara and Eva. Ben has the rating but not the sales. Chloe has the sales but a rating of 3. Two of six people pass.

Rule 2 with OR: sales of 50,000 or more or a rating of 4 or more#

=OR(B2>=50000,C2>=4)

Now five people pass: everyone except Dev, who misses both conditions. Same two conditions, very different result.

Turning TRUE and FALSE into labels with IF#

On its own, AND or OR shows TRUE or FALSE. Wrap it in IF to show something more useful:

=IF(AND(B2>=50000,C2>=4),"Bonus","No")
=IF(OR(B2>=50000,C2>=4),"Review","")

The first shows Bonus for Amara and Eva. The second marks five people for a review and leaves Dev blank. The guides to the IF AND formula and the IF OR formula have more examples of each pattern.

Counting rows that pass#

COUNTIFS uses AND logic: every condition must match. =COUNTIFS(B2:B7,">=50000",C2:C7,">=4") returns 2. For OR, add two COUNTIFs and subtract the rows counted twice: =COUNTIF(B2:B7,">=50000")+COUNTIF(C2:C7,">=4")-COUNTIFS(B2:B7,">=50000",C2:C7,">=4") gives 3 + 4 − 2 = 5.

When to use AND#

Use AND when every condition is required. Listen for the words "both", "all" and "and also":

• Eligibility rules: a bonus needs high sales and a good rating.
• Ranges between two numbers: =AND(B2>=40000,B2<=60000) is TRUE when sales are from 40,000 to 60,000. Ben, Chloe and Femi pass. The guide to IF between two numbers covers this in detail.
• Data checks: a row is complete only if the name, date and amount are all filled in: =AND(A2<>"",B2<>"",C2<>"").
• Conditional formatting rules: select A2:D7, choose Home > Conditional Formatting > New Rule > Use a formula, and enter =AND($B2>=50000,$C2>=4) with a green fill. Amara's and Eva's rows light up. The $ before B and C keeps every cell in the row checking the same two columns.

When to use OR#

Use OR when any one condition is enough. Listen for "either", "any" and "at least one":

• Several accepted values: a region is in the southern team if it's South or West: =OR(D2="South",D2="West").
• Warning flags: flag a row if sales are low or the rating is low: =OR(B2<40000,C2<3) flags only Dev.
• Alternative routes: a candidate qualifies with a degree or five years' experience.
• Finding gaps: =OR(B2="",C2="",D2="") is TRUE when any of the three cells is empty, a quick way to spot incomplete rows before you share a report. It's the mirror image of the AND data check above.

For "one or the other, but not both", use XOR instead. The guide to the XOR function explains how it differs from OR.

Using AND and OR together#

Real rules often mix both. Nest one function inside the other. For example, staff in the North or South region with sales of at least 45,000:

=AND(OR(D2="North",D2="South"),B2>=45000)

The OR checks the region, then AND adds the sales condition. Amara, Ben, Chloe and Femi pass: 4 people. Dev and Eva fail the region test.

Read nested formulas from the inside out, and say the rule in words before you type it. "North or South, and at least 45,000" maps directly onto the formula. The guide to AND, OR and NOT adds NOT, which flips TRUE and FALSE, for rules like "any region except East".

Common mistakes with AND and OR#

These are the slips that most often give wrong answers:

1. Choosing by the word in the question, not the logic. "Staff in North and South" usually means staff in either region, which is OR. No one is in both regions at once, so AND would return FALSE for everyone.
2. Writing the cell once for two tests. =OR(D2="North","South") doesn't work. Repeat the cell: =OR(D2="North",D2="South").
3. Using AND or OR inside FILTER. =FILTER(A2:A7,AND(B2:B7>=50000,C2:C7>=4)) fails, because AND returns one value for the whole range. Use (B2:B7>=50000)*(C2:C7>=4) instead, which returns Amara and Eva. Use + for OR. FILTER needs Excel 2021 or Microsoft 365.
4. Writing maths-style ranges. =40000<=B2<=60000 doesn't work in Excel. Use =AND(B2>=40000,B2<=60000).
5. Forgetting quotes around text. D2=North gives #NAME?. Write D2="North".

Our recommendation#

Choose by how many conditions must be true. If all of them must be true, use AND. If any one is enough, use OR. When a rule has both kinds, nest them, and write the rule in plain words first. In FILTER and other array formulas, swap the functions for * (AND) and + (OR). If you're unsure which one a rule needs, test it on a row that meets only one condition, like Ben or Chloe here, and check that the result matches what the rule intends.

In short: AND for all, OR for any.

Practise it#

Try the exercise on this page: an IF with AND that pays a bonus only when both conditions are met. Then practise AND, OR and NOT in a live spreadsheet, where you combine conditions and check your answers instantly.