How to Use IF With AND and OR in Excel

A plain IF formula answers one yes-or-no question. Real rules at work usually have more than one part: "a rep gets a bonus if sales are at least 10,000 and they closed at least four deals," or "flag the account if it is VIP or spends over 20,000." Excel handles this by putting AND, OR, or NOT inside IF. Once you see how the parentheses line up, the pattern is the same every time.

1. Start with plain IF

Microsoft's IF documentation gives the syntax as:

IF(logical_test, value_if_true, [value_if_false])

The first argument is the test, the second is what to return when the test is true, and the third (optional) is what to return when it is false. Microsoft's simple example is =IF(C2="Yes",1,2): return 1 if C2 says Yes, otherwise 2. Microsoft also notes that any text you want returned must be wrapped in quotes, with TRUE and FALSE as the only exception.

If you leave out the third argument and the test is false, Excel returns FALSE. For example, =IF(G12>=10000,"Yes") with 9,800 in G12 shows FALSE, which looks odd in a report. Give IF both answers.

2. How AND, OR, and NOT fit inside

Microsoft's page describes the three combinations like this:

• AND: all conditions must be true. =IF(AND(condition1, condition2), value_if_true, value_if_false)
• OR: only one condition needs to be true. =IF(OR(condition1, condition2), value_if_true, value_if_false)
• NOT: reverses a single condition. =IF(NOT(condition), value_if_true, value_if_false)

The key detail, which Microsoft points out, is where the parentheses close. The AND, OR, or NOT closes right after its conditions. The two result values belong to the outer IF, not to AND.

Microsoft's own examples return exactly what the page says when entered in Excel. With 25 in A2 and 75 in B2, =IF(AND(A2>0,B2<100),TRUE,FALSE) returns TRUE, because both conditions hold. With 25 in A4 and 75 in B4, =IF(OR(A4>0,B4<50),TRUE,FALSE) also returns TRUE, because OR only needs the first condition. And with 25 in A6, =IF(NOT(A6>50),TRUE,FALSE) returns TRUE, because 25 is not greater than 50.

AND and OR accept up to 255 conditions, according to Microsoft, but the page also warns that formulas with more than a few become hard to build, test and maintain. NOT takes only one.

3. A worked example: sales bonus rules

Here is a small team table:

Rule 1: Bonus if sales are at least 10,000 and deals are at least 4.

=IF(AND(G11>=10000,H11>=4),"Bonus","No")

Filled down for the four rows, this returns Bonus for Lee and No for everyone else. Kim has the deals but not the sales. Park and Cho have the sales but not the deals. Note that >= means "at least," so Cho's 10,000 counts as meeting the sales condition.

Rule 2: Priority if the tier is VIP or sales are at least 20,000.

=IF(OR(I11="VIP",G11>=20000),"Priority","Standard")

This returns Standard for Lee and Priority for Kim, Park and Cho. Kim and Cho qualify through the tier, Park through sales.

Rule 3: Follow up unless the status is Closed.

=IF(NOT(J11="Closed"),"Follow up","")

This returns "Follow up" for Lee, Park and Cho, and leaves Kim's cell empty. The pair of quotes with nothing between them is how you return a blank-looking result.

4. Combining AND with OR

Rules can nest. Suppose the bonus policy changes to: sales of at least 10,000, plus either four deals or VIP tier.

=IF(AND(G11>=10000,OR(H11>=4,I11="VIP")),"Bonus","No")

Read it from the inside out. The OR checks the "either" part. Its result becomes the second condition of AND. With this rule, Lee and Cho get Bonus, while Kim (sales too low) and Park (two deals, not VIP) do not.

Count the parentheses as you type: each function you open needs its own closing parenthesis before the final value arguments of the IF.

5. Using the conditions without IF

You do not always need IF. =AND(G11>=10000,H11>=4) on its own returns TRUE for Lee and FALSE for the others. Microsoft's page shows that AND, OR and NOT can be used this way in conditional formatting rules, where the formula only needs to produce TRUE or FALSE. In a normal cell, IF is what turns TRUE and FALSE into labels people can read.

6. Common problems

Microsoft's IF page lists two that come up often:

• #NAME? in the cell usually means a function name is misspelled.
• 0 in the cell means a value argument was left empty. Fill in both results.

Also check that text comparisons use quotes, as in I11="VIP". A comparison with a number, such as G11>=10000, does not.

7. Key takeaways

• IF answers one question; put AND, OR, or NOT inside it to test several.
• AND needs every condition true, OR needs at least one, NOT reverses one condition.
• Close the inner function's parentheses before the IF's true and false values.
• You can nest OR inside AND to express "this, plus either of these."
• Always give IF both a true and a false result to avoid stray FALSE values.