How to Use the AND and OR Functions in Excel (With IF Examples)

shape
shape
shape
shape
shape
shape
shape
shape
How to Use the AND and OR Functions in Excel (With IF Examples) - featured image

The AND and OR functions in Excel let you test more than one condition at the same time. AND returns TRUE only when every condition is true, and OR returns TRUE when at least one condition is true. On their own they only give TRUE or FALSE, but combined with the IF function they become the base of most decision formulas, such as awarding a bonus only when sales are high and the customer rating is good, or flagging an order that is late or unpaid. This guide explains the syntax, shows truth tables, gives worked examples with grades, bonuses, dates and discounts, shows how to nest AND inside OR, introduces NOT, and lists the common mistakes with fixes.

Updated November 2026

In this guide

  1. What the AND function does
  2. What the OR function does
  3. Truth table
  4. Use AND with IF
  5. Use OR with IF
  6. Example 1: sales bonus
  7. Example 2: grade between two values
  8. Example 3: check a date range
  9. Example 4: shipping discount with OR
  10. Combine AND and OR
  11. The NOT function
  12. Use AND and OR without IF
  13. AND and OR inside SUMIFS and COUNTIFS
  14. Tips for writing clear logical formulas
  15. Error handling
  16. Common mistakes
  17. Practice problems
  18. Quick recap
  19. Real-world examples

What the AND function does

The syntax is =AND(logical1, logical2, ...). You can enter up to 255 conditions. AND returns TRUE if all of them are true, and FALSE if even one is false. For example, if A2 holds 85 and B2 holds 90, then =AND(A2>=80,B2>=80) returns TRUE, since both scores are at least 80. If B2 held 70, the result would be FALSE.

What the OR function does

The syntax is =OR(logical1, logical2, ...). OR returns TRUE if at least one condition is true, and FALSE only when all of them are false. With A2 equal to 85 and B2 equal to 70, =OR(A2>=80,B2>=80) returns TRUE, because the first test is true. It returns FALSE only when both scores are below 80.

Truth table

This table shows how the two functions work for two conditions.

Condition 1Condition 2ANDOR
TRUETRUETRUETRUE
TRUEFALSEFALSETRUE
FALSETRUEFALSETRUE
FALSEFALSEFALSEFALSE

A quick way to remember the difference is that AND is strict and OR is flexible.

Use AND with IF

The IF function takes one test. To put two tests in it, wrap them in AND. The pattern is =IF(AND(test1,test2),"yes","no"). Suppose a student passes only when the exam score is at least 50 and attendance is at least 75 percent. With the score in B2 and attendance in C2, the formula is =IF(AND(B2>=50,C2>=75),"Pass","Fail"). A student with 60 and 80 passes. A student with 60 and 70 fails, even though the score is good. Our IF statements guide covers the basics of IF.

Use OR with IF

With OR, the same pattern =IF(OR(test1,test2),"yes","no") flags a row when any condition is met. For an order list where an order needs attention if it is more than 5 days old or marked unpaid, with the age in B2 and the status in C2, use =IF(OR(B2>5,C2="Unpaid"),"Check","OK"). An order that is 2 days old and unpaid returns Check. An order that is 8 days old and paid also returns Check. An order that is 2 days old and paid returns OK.

Example 1: sales bonus

A company pays a bonus of 500 when sales are at least 10000 and the customer rating is at least 4. With sales in B2 and the rating in C2, use =IF(AND(B2>=10000,C2>=4),500,0). Sales of 12000 with a rating of 4.5 give 500. Sales of 12000 with a rating of 3.8 give 0. Sales of 9000 with a rating of 5 also give 0. If you want to calculate the bonus as a share of sales, see our percentage guide.

Example 2: grade between two values

To test that a value lies in a range, use AND with two comparisons. A score is a B when it is at least 80 and below 90. The formula =IF(AND(A2>=80,A2<90),"B","Other") returns B for 85 and Other for 90, because 90 is not below 90. This is a common way to build a grade table. For long lists of grades, the nested IF or the lookup approach in our final grade guide is easier to read.

Example 3: check a date range

To check whether a date falls within a period, use AND with two date comparisons. For a promotion that runs from 1 November 2026 to 30 November 2026, with the order date in A2, use =IF(AND(A2>=DATE(2026,11,1),A2<=DATE(2026,11,30)),"Promo","No promo"). An order on 18 November returns Promo, and an order on 2 December returns No promo. Our DATEDIF guide covers more date work.

Example 4: shipping discount with OR

A shop gives free shipping if the order total is at least 50 or the customer is a member. With the total in B2 and the status in C2, the formula is =IF(OR(B2>=50,C2="Member"),0,5). A member with a total of 20 pays 0 for shipping. A non member with a total of 60 pays 0. A non member with a total of 20 pays 5.

Combine AND and OR

You can nest the two functions for more complex rules. Suppose a discount applies when the customer is a member and the order is at least 100, or when the coupon code is VIP. The formula is =IF(OR(AND(C2="Member",B2>=100),D2="VIP"),"Discount","None"). A member with 120 gets Discount. A member with 80 and no coupon gets None. A non member with the VIP coupon gets Discount. Always check the brackets, since a misplaced bracket changes the meaning of the rule.

The NOT function

NOT reverses a result. =NOT(A2="Paid") returns TRUE when the cell is anything other than Paid. It is useful with AND and OR, for instance =IF(AND(B2>0,NOT(C2="Cancelled")),"Count","Skip"). You can often write the same thing with the not equal sign, such as C2<>"Cancelled", which is shorter.

Use AND and OR without IF

Because they return TRUE or FALSE, AND and OR can be used in conditional formatting rules, in data validation and in filters. To highlight rows in which the score is above 80 and the status is Active, create a rule with the formula =AND($B2>80,$C2="Active"). They also work in SUMPRODUCT. A multiplication of two tests acts like AND, and an addition acts like OR, which is explained in our SUMIFS guide as an alternative for conditional sums.

AND and OR inside SUMIFS and COUNTIFS

The functions SUMIFS and COUNTIFS already treat several criteria as AND. For OR logic, add two COUNTIFS together. To count orders that are Paid or Pending, use =COUNTIFS(C:C,"Paid")+COUNTIFS(C:C,"Pending"). Take care that a row cannot match both, or it will be counted twice. See the COUNTIF page for more.

Tips for writing clear logical formulas

Keep each test simple and put the limits in cells, not inside the formula. If the bonus threshold of 10000 sits in cell F1, write =IF(AND(B2>=$F$1,C2>=$F$2),500,0), and you can change the rules without editing every formula. Use helper columns when a rule has many parts, with one column for each test and a final column that combines them. This makes errors easy to find. Add a short note that explains the rule in words, so the next person understands it. Finally, test the borders: try a value exactly on the limit, one just above and one just below, to confirm that you used the right comparison sign.

Error handling

If one of the cells contains an error, such as a division error, AND and OR also return that error. Wrap the test in IFERROR if the data may be messy, for example =IFERROR(IF(AND(B2>=50,C2>=75),"Pass","Fail"),"Check data"). Empty cells are treated as zero in number comparisons, so a blank score of zero may count as a fail. If that is not what you want, add a test such as B2<>”” to the AND.

Common mistakes

The first mistake is to write the conditions in the wrong form, such as A2>50 AND B2>50, since Excel needs the function AND(…). The second is to use text without quotation marks. The third is to write a range test as 50<A2<90, which does not work and must be AND(A2>50,A2<90). The fourth is to use AND when you mean OR. A good habit is to read the rule aloud and check whether the word is all or any. The fifth is to forget that the tests are case insensitive for text. The sixth is to lose a bracket in nested formulas.

Practice problems

Try these. Problem 1: write a formula that returns Yes when a score is above 60 and the age is under 30. Problem 2: write a formula that returns Late when the days are over 10 or the status is Open. Problem 3: for a score of 75 and attendance of 70, what does =AND(A2>=70,B2>=75) return? The answer is FALSE, as 70 is below 75. Problem 4: what does =OR(A2>100,B2>100) return for 90 and 95? The answer is FALSE.

Quick recap

AND needs every test to be true and OR needs only one. Put them inside IF to get a result other than TRUE or FALSE. Nest AND inside OR for mixed rules, use NOT to reverse a test, and use them in conditional formatting too. Check each rule by reading it aloud, and test the formula with values on both sides of each limit.

Real-world examples

An HR sheet can use AND to check that an employee has both the required years of service and a good review before a raise. A store can use OR to flag items that are low in stock or past the expiry date. A school can use AND to confirm that a student passed every subject. A project plan can use OR to mark a task as at risk if it is late or over budget. These checks run instantly on thousands of rows, which saves hours of manual review.

Frequently asked questions

What does the AND function do in Excel?

It returns TRUE only when all of its conditions are true.

What does the OR function do in Excel?

It returns TRUE when at least one of its conditions is true.

How do I use AND with IF?

Write =IF(AND(test1,test2),value_if_true,value_if_false).

Can I nest AND inside OR?

Yes, you can combine them to build complex rules.

How many conditions can AND have?

Up to 255.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *