Excel IF with AND and OR: testing multiple conditions

In short: =AND(B2>=C2,D2>=4) is TRUE when both conditions hold. =OR(B2>=50,C2="Premium") is TRUE when one of them does. Inside IF they take the place of the logical test: =IF(AND(…),value_if_true,value_if_false).

Try it now

The bonus is paid only when two things are true together: the sales target is met and the rating is high enough. Write the formula for the first row; it is then filled down the column.

Task A bonus of 500 is paid only if sales reach at least the target and the rating is at least 4. Otherwise the bonus is 0. Write the formula in E2, it is filled down to E7.

	A	B	C	D	E
1	Name	Sales $	Target $	Rating	Bonus $
2	Jana Weber	52400	50000	4
3	Murat Kaya	61800	55000	3
4	Lena Hoffmann	47300	50000	5
5	Tom Schulz	58000	58000	4
6	Sara Klein	39900	45000	2
7	Ali Demir	66100	60000	5

How AND, OR and NOT work

None of the three returns a number or a piece of text. They only ever return TRUE or FALSE. Each condition is a comparison of the kind you know from the IF function, and the conditions are separated by commas.

Function	Returns TRUE when	Example
AND	every condition is true	=AND(B2>=C2,D2>=4)
OR	at least one condition is true	=OR(B2>=50,C2="Premium")
NOT	the condition is false (it flips the result)	=NOT(C2="Premium")

AND and OR accept up to 255 conditions. A quick way to remember the difference: AND is strict, a single FALSE makes the whole thing FALSE. OR is generous, a single TRUE is enough.

Putting AND and OR inside IF

On its own, AND just shows TRUE or FALSE. Usually you want a label or an amount instead, so you use AND or OR as the test of an IF:

• =IF(AND(B2>=C2,D2>=4),500,0) pays a bonus of 500 when the target is met and the rating is 4 or higher.
• =IF(OR(B2>=50,C2="Premium"),0,4.95) makes shipping free when the order is at least 50 or the customer has a Premium account.
• =IF(NOT(C2="done"),"open","") flags everything that is not finished yet. C2<>"done" does the same with less typing.

Watch the brackets. AND gets its own pair, and only after it closes comes the comma that starts value_if_true. A common slip is =IF(AND(B2>=C2,D2>=4,500,0)): now 500 and 0 are treated as extra conditions inside AND, and IF has no value to return.

One more: shipping costs with OR

This time one condition out of two is enough. Pay attention to the order of exactly 50.

Task Shipping is free if the order amount is at least 50 or the customer type is Premium. Otherwise shipping costs 4.95. Write the formula in D2, it is filled down to D8.

	A	B	C	D
1	Order	Amount $	Customer type	Shipping $
2	SO-1043	72.5	Standard
3	SO-1038	38.9	Premium
4	SO-1051	24	Standard
5	SO-1029	50	Standard
6	SO-1047	129.99	Premium
7	SO-1055	45.1	Standard
8	SO-1032	18.75	Premium

Combining AND with OR

The functions can be nested in each other. Here a bonus goes to everyone in Sales who either met their target or won more than 20 new customers:

=IF(AND(A2="Sales",OR(B2>=C2,D2>20)),"Bonus","")

Read formulas like this from the inside out. Excel evaluates the OR first, then the AND that uses its result. Once you go deeper than two levels, a helper column for each partial test is often the better choice, because you can see every step.

AND as a conditional formatting rule

Because AND already returns TRUE or FALSE, a conditional formatting rule does not need IF at all. The rule highlights every cell where the formula is TRUE. With $ in front of the column letters, it colors the entire row.

Task Use conditional formatting to highlight the whole rows A2:C8 for invoices of at least 1000 that have been open for more than 30 days. Write the rule formula as it applies to the first cell A2.

	A	B	C
1	Invoice	Amount $	Days open
2	INV-2041	1850	45
3	INV-2038	420	62
4	INV-2050	2300	12
5	INV-2033	1000	31
6	INV-2047	760	8
7	INV-2029	3150	74
8	INV-2052	1290	30

Put the same formula into a column of its own and you get a check column of TRUE and FALSE values that you can filter or count. If what you really want is a total across several conditions, SUMIFS gets you there in one step.

Common mistakes with AND and OR

Leaving out half of a comparison

=AND(B2>=1000,<=5000) does not work. Each condition has to be a full comparison: =AND(B2>=1000,B2<=5000). In the same way, OR(B2="Boston","Chicago") is wrong; write OR(B2="Boston",B2="Chicago").

Using AND when you mean OR

People say "customers in Boston and Chicago" when they mean customers in either city. A single cell cannot hold both values, so AND would return FALSE for every row. For each rule, ask yourself: must everything be true, or is one enough?

Text without quotes

C2=Premium gives #NAME? because Excel reads Premium as a name. Text always needs straight double quotes: C2="Premium". Upper and lower case do not matter.

AND, OR and NOT in German Excel

German Excel uses semicolons between arguments instead of commas, and the functions have German names:

English	German
AND, OR, NOT	UND, ODER, NICHT
XOR	XODER
TRUE, FALSE	WAHR, FALSCH
=IF(AND(B2>=C2,D2>=4),500,0)	=WENN(UND(B2>=C2;D2>=4);500;0)

Our exercises accept both spellings. Pick your Excel language below each exercise.

Which Excel versions have AND and OR?

AND, OR and NOT are in every version of Excel, including Excel 2016, Excel for Mac and Excel for the web, so sharing a file that uses them is never a problem. XOR returns TRUE when an odd number of conditions are true, which with two conditions means exactly one. Microsoft lists XOR for Excel 2016 and later and Microsoft 365.

Frequently asked questions