Cellwise Try it free

Excel guide · 3 min read

Excel IF statement with multiple conditions

Quick answerPut the conditions inside AND or OR, then use that as the IF test. =IF(AND(C2>=50000,D2>=3),"Bonus","No bonus") returns "Bonus" only when both are true; swap AND for OR to return it when either is true. For more than two possible results, use IFS or a nested IF.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1RepRegionSalesYears
2MariaWest520004
3JamesEast380002
4PriyaWest610001
5TomNorth450006
6LenaEast705003
7OmarNorth290001

All conditions must be true: IF with AND

AND returns TRUE only when every test inside it is TRUE. Feed it to IF and you get one result when everything passes and another when anything fails.

Here a rep earns a bonus only with at least $50,000 in sales and at least 3 years on the team. Maria has both, so she qualifies. Priya sold more but has 1 year, so she does not.

=IF(AND(C2>=50000,D2>=3),"Bonus","No bonus")

Result: Bonus

Maria has 52,000 in sales and 4 years, so both tests pass and the result is Bonus.

Any condition can be true: IF with OR

OR returns TRUE if at least one test is TRUE. Use it when several different things should lead to the same result.

This flags a rep as Priority if they work the West region or have sold $60,000 or more. Lena (East, 70,500) and Priya (West) both get flagged, for different reasons.

=IF(OR(B2="West",C2>=60000),"Priority","Standard")

Result: Priority

Maria is in the West region, so the first test is TRUE and the result is Priority.

Mix AND and OR in one test

You can nest AND inside OR (or the other way round) to describe real business rules. Read it from the inside out.

Rule: review the account if it is a West rep with $50,000+ in sales, or any rep with 5+ years. Tom qualifies on tenure alone. Maria qualifies on the West rule.

=IF(OR(AND(B2="West",C2>=50000),D2>=5),"Review","-")

Result: Review

Maria matches the AND part (West and 52,000), so OR is TRUE and the result is Review.

More than two outcomes: IFS (Excel 2019 and later)

AND and OR still give only two answers. When you need three or more bands, use IFS. It checks each condition in order and returns the value paired with the first one that is TRUE.

Put the strictest condition first. End with TRUE as a catch-all, otherwise a value that matches nothing returns #N/A. In Excel 2016 or older, IFS does not exist; use a nested IF instead.

=IFS(C2>=60000,"Gold",C2>=45000,"Silver",TRUE,"Bronze")

Result: Silver

Maria's 52,000 fails the Gold test but passes Silver, so IFS stops there and returns Silver.

Count or sum rows that meet several conditions

If you want a total rather than a label on each row, skip IF and use COUNTIFS or SUMIFS. They take pairs of range and criteria and only count rows where every pair matches.

This counts West reps with at least $50,000 in sales without adding any helper column.

=COUNTIFS(B2:B7,"West",C2:C7,">=50000")

Result: 2

Maria and Priya are both West reps above 50,000, so the count is 2.

Common mistakes

Writing the conditions like a sentence

=IF(C2>50000 AND D2>3, ...) is not valid. AND and OR are functions: AND(C2>50000,D2>3).

Chaining comparisons such as =IF(40000<C2<60000, ...)

Excel evaluates 40000<C2 first, gets TRUE, then compares TRUE with 60000, which is always FALSE. Use AND(C2>40000,C2<60000).

Typing text conditions without quotes

B2=West gives #NAME?. Text must be in double quotes: B2="West". Numbers do not need quotes.

Putting a loose condition first in IFS

IFS stops at the first TRUE. If C2>=45000 comes before C2>=60000, every Gold rep shows Silver. Order from strictest to loosest.

FAQ

How many conditions can I put in one IF?

AND and OR accept up to 255 conditions, and you can nest up to 64 IF functions. In practice, more than four or five nested levels is a sign you should use IFS or a lookup table.

Can I use AND and OR together in Excel?

Yes. Nest one inside the other, for example =IF(OR(AND(A2="West",B2>100),C2>5),"Yes","No"). Each AND or OR becomes a single TRUE or FALSE that the outer function uses.

How do I return a blank instead of "No"?

Use two double quotes as the false result: =IF(AND(C2>=50000,D2>=3),"Bonus",""). The cell looks empty but holds an empty text string.

Is IFS better than nested IF?

IFS is easier to read and edit because the conditions sit side by side instead of inside each other. Use nested IF only if the file must work in Excel 2016 or older.

Functions used

Related guides

Updated