IF With Multiple Conditions in Excel

To use IF with multiple conditions in Excel, put AND or OR inside the IF test: AND when every condition must be true, OR when any one is enough. When you need three or more different results, such as Gold, Silver and Bronze, use a nested IF or the IFS function. This guide walks through each pattern on one small sales table, with the exact formulas and results.

All the examples use this table in A1:D7:

Which formula do you need?#

The right formula depends on how the conditions combine and how many results you want:

IF with AND: all conditions must be true#

AND returns TRUE only when every test inside it is true. IF then picks a result based on that one TRUE or FALSE. In E2:

=IF(AND(B2>=50000,D2>=2),"Bonus","No bonus")

Fill it down to E7 (Windows: Ctrl+D; Mac: Cmd+D). Aisha, Dev and Femi get Bonus. Ben misses on sales, and Carla misses on years: she sold 75,000 but has only one year. That is AND at work. One false test is enough to make the whole thing false.

The IF AND formula guide covers this pattern in more depth, and the AND function guide lists its rules. A very common use is a range check, such as "between 40,000 and 60,000": =IF(AND(B2>=40000,B2<=60000),"Mid","Other"). See how to check if a number is between two values.

IF with OR: any condition can be true#

OR returns TRUE when at least one test is true. Say a rep gets a review if they sold 70,000 or more or have four or more years:

=IF(OR(B2>=70000,D2>=4),"Review","")

Ben (5 years), Carla (75,000) and Femi (both) get Review. Aisha, Dev and Elena meet neither test, so the cell shows nothing, because "" is an empty text result.

To test one cell against several values, OR is handy too: =IF(OR(C2="North",C2="East"),"Team A","Team B").

IF with NOT: when a condition must be false#

NOT flips TRUE to FALSE and back. These two formulas do the same thing:

=IF(NOT(C2="South"),"Yes","No")
=IF(C2<>"South","Yes","No")

<> means "not equal to" in Excel. Most people find it easier to read than NOT for a single test. NOT is more useful around a bigger test, such as NOT(OR(...)) for "neither of these". The AND, OR and NOT lesson practises all three.

Combining AND and OR in one IF#

You can put OR inside AND (or the other way round) for rules like "North or East, and sales of at least 55,000":

=IF(AND(OR(C2="North",C2="East"),B2>=55000),"Yes","No")

Excel works from the inside out. First OR checks the region. Then AND checks that result together with the sales test. Aisha, Carla, Dev and Femi return Yes. Ben and Elena are in South, so OR is FALSE and the answer is No.

With several brackets, it helps to build the formula in pieces: write and test the OR on its own, then wrap it in AND, then wrap that in IF. The guide to using IF, AND and OR together has more mixed examples.

Nested IF and IFS: more than two results#

When each condition leads to a different result, put one IF inside another. Here is a tier for each rep:

=IF(B2>=75000,"Gold",IF(B2>=50000,"Silver","Bronze"))

Carla and Femi get Gold, Aisha and Dev Silver, Ben and Elena Bronze. The order matters: test the highest threshold first. If you test B2>=50000 first, Carla's 75,000 stops there and gets Silver.

In Excel 2019, Excel 2021 and Microsoft 365, the IFS function does the same with fewer brackets:

=IFS(B2>=75000,"Gold",B2>=50000,"Silver",TRUE,"Bronze")

IFS checks each test in order and returns the result next to the first true one. TRUE at the end is a catch-all for everything else. Without it, a value that matches no test gives #N/A. Read more in the nested IF guide and in nested IF vs IFS, and see the IF function guide for the basic syntax.

Multiple conditions with text and dates#

The same patterns work with text and dates, not just numbers. Text goes in double quotes, and a date is safest built with the DATE function, so Excel can't misread it as text. For example, if column F held a start date:

=IF(AND(C2="North",F2<DATE(2024,1,1)),"Senior North","Other")

This returns "Senior North" only for North reps who started before 1 January 2024. Text tests are not case-sensitive, so "north" matches "North". To match part of a word, such as any region containing "east", wrap SEARCH in ISNUMBER: ISNUMBER(SEARCH("east",C2)) is TRUE for East, North-East and Southeast.

Common mistakes with multiple conditions#

• Writing AND between the tests. =IF(B2>=50000 AND D2>=2,...) is not valid. AND is a function: AND(B2>=50000,D2>=2).
• Chaining comparisons. =IF(40000<B2<60000,...) does not work as it reads. Excel compares 40000<B2 first, gets TRUE or FALSE, then compares that with 60000. Use AND(B2>40000,B2<60000).
• Forgetting quotes around text. C2=North gives #NAME?. Write C2="North".
• Testing in the wrong order in a nested IF, so a high value stops at a low tier.
• Using AND inside COUNTIFS or SUMIFS. They do not need it. For counts, list the conditions as pairs: =COUNTIFS(B2:B7,">=50000",D2:D7,">=2") returns 3.
• Missing brackets. Every function you open needs a closing bracket. Excel colours each pair as you type, which helps you check.

Practise it#

Reading formulas is easy; typing them correctly under a timer is what tests check. Practise nested IF and IFS in a live spreadsheet: you write each formula and it checks your answer instantly. Then try the bonus exercise on this page. Not sure where your logic skills stand? Take the Excel level test to find the right lesson to start with.