One of the most frequently used Excel functions is IF statements. With it, you can run logical tests and base your decisions on the results. But do you know how to run the IF function with multiple conditions?
Excel IF statements with multiple conditions allow you to categorize data, apply conditional formatting, display customizable messages, do computations depending on particular situations, and more. Read on to learn everything you need to know about Excel’s powerful spreadsheet tool.
Table of Contents
IF Function Syntax
The IF statement evaluates a condition and returns one value if the condition is true and another value if the condition is false. The IF function in Excel is a function that usually works with one condition, just like in Google Sheets. If you are a regular reader here at Spreadsheet Point, you’ll know how much I like to break down the syntax of a function. It helps me understand exactly what is needed. So, here’s the syntax of the IF statement in Excel:
=IF(logical_test, value_if_true, value_if_false)
- logical_test: This is the condition that you want to evaluate. It can be a comparison between values, a formula that returns a logical value (TRUE or FALSE), or a cell reference containing a logical value.
- value_if_true: This value will be returned if the logical_test is true. It can be a specific value, a cell reference, or a formula.
- value_if_false: This value will be returned if the logical_test is false. It can also be a specific value, a cell reference, or a formula.
The IF statement only allows for one condition. However, you can use multiple criteria with the IFS function. But it is also possible to use Excel IF with multiple conditions by nesting it with the following functions, such as:
- AND
- OR
- More IF statements
Note that the IF function is similar to the SUMIF function in Google Sheets. Both allow you to use one condition to perform an action. The IFS function uses multiple-condition logic to perform an action in Microsoft Excel. The syntax for this function looks a little different.
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)
- logical_test: Just like with IF, the logical test in the IFS function is what you’re choosing to evaluate. Note that there are many of these in IFS, where there’s only one in the IF function syntax.
- value_if_true: Again, this is similar to what we discussed with the IF syntax above. This tells the formula what value to return if your logical test returns TRUE.
However, we’ll use the AND function with the IF Function in Excel with multiple conditions. It’s another method to let you evaluate multiple criteria without having to use the IFS function.
Download Our Example Worksheet for Excel IF Statements With Multiple Conditions
Let’s look at some examples with the following sample worksheet:
Using IF With AND to Make an Excel if Statement With Multiple Conditions Across a Range
The AND function returns TRUE if all of the specified conditions are true. It evaluates multiple logical expressions and returns a single TRUE or FALSE result. When used with the IF, it allows you to have multiple conditions.
The syntax for the Excel IF multiple criteria formula with AND is:
=IF(AND(condition1, condition2, condition3), value_if_true, value_if_false)
You can also use more than three conditions if you’d like to. As an example, let’s find the sales that were above 12,000 in January AND February in our sample sheet and label the salesperson’s output as “Good” or “Bad”:
- Type the equals sign (=) and the IF function.
- Add the AND function after the opening brackets.
- Select the first cell and add the first condition. In our example, we’ll use greater than or equal to (>=) 12,000.
- Add a comma and select the second cell.
- Add the second condition. In our example, we’ll use greater than equal to (>=) 12,000.
- Close the brackets and add a comma
- Add the value to return if True. We’ll be using “Good.”
- Add the value to return if False. We’ll be using “Bad.”
- Copy the formula to the rest of the cells.
This IF formula in Excel with multiple conditions returns Good if both months had sales more than or equal to 12,000 and bad otherwise.
[adthrive-in-post-video-player video-id=”zuNqR8CK” upload-date=”2023-12-04T15:14:19.000Z” name=”IF Function in Google Sheets: Logical Tests” description=”How do you use the IF function for logical tests? I covered it in this video. We specifically discuss how, when IF contains partial text, to return a logical result.” player-type=”default” override-embed=”default”]
Related: The Best Excel Courses on the Market Today
Using the OR Function Instead of AND
The OR function returns TRUE if at least one of the specified conditions is true. It evaluates multiple logical expressions and returns a single TRUE or FALSE result.
The formula for using AND in the IF statement is:
=IF(OR(condition1, condition2, condition3), value_if_true, value_if_false)
As you can see, it looks very similar to an AND/ IF function, so you can follow similar steps to create the full formula.
Let’s create another formula to label the sales as “Good” or “Bad” if the salesperson achieved more than $15,000 in sales in either month.
- Type the equals sign (=) and the IF function.
- Add the OR function after the opening brackets.
- Select the first cell and add the first condition. In our example, we’ll use greater than equal to (>=) 15,000.
- Add a comma and select the second cell.
- Add the second condition. In our example, we’ll use greater than equal to (>=) 15,000.
- Close the brackets and add a comma
- Add the value to return if True. We’ll be using “Good.”
- Add the value to return if False. We’ll be using “Bad.”
- Copy the formula.
This formula returns “Good” if one of the months had sales more than or equal to 15,000 and “Bad” otherwise.
Nesting Several IF Statements
In a similar fashion to what we did with AND & OR above, you can nest several IF functions into one formula.
The nested IF function in Excel allows you to create more complex logical tests and decision-making scenarios by nesting one IF function within another. With nested IF statements, you can have multiple conditions in an IF statement and select varied outputs.
For example, if you have marks and you want to convert them to grades from A to E, this could be a way to do it.
Here’s how the nested IF function works:
=IF(logical_test1, value_if_true1, IF(logical_test2, value_if_true2, value_if_false))
Let’s look at our example worksheet again. We can have the formula return “East” if the location is New York, “West” if it’s California, and “Central” if it’s Florida.
- Write the IF function and the first condition B2=California, “West.”
- Add a comma and write the second condition IF B2=New York, “East.”
- Add a comma and the third condition.
The complete formula is:
=IF(B2="California","West",IF(B2="New York", "East", IF(B2="Florida","Central")))
The formula returns East, West, or Central depending on the corresponding location, as shown in the example above.
Excel IF Array Formula with Multiple Conditions
Using an array formula with multiple conditions within an IF function in Excel allows you to perform advanced logical tests and make decisions based on multiple criteria. Array formulas extend the capabilities of regular formulas by applying calculations across a range of cells rather than a single cell.
Here’s how you can use an array formula with IF and multiple conditions in Excel. Using IF with the array formula is similar to how it works with AND & OR. To have the formula work like AND, we use an asterisk sign (*). To have it work like OR, we use the plus sign (+).
For example, if we wanted to show people with sales worth more than 12,000 in both months with AND. We’d use the formula:
=IF((C2>=12000)*(D2>=12000),"Good", "Bad")
To make it an array formula, you can use the keyboard shortcut Ctrl + Shift + Enter after entering the formula. Or you can manually add brackets to the outside of the entire formula.
On the other hand, if we wanted to find people with more than 12,000 sales in either month, like when using OR. We’d use the following formula:
=IF((C2>=12000)+(D2>=12000),"Good", "Bad")
Using IF Together with Other Functions
We can also use the Excel IF function with multiple conditions with other functions to create more complex formulas. This means you can use IF along with OR statements or with the AND function to produce more general or more specific results. You can also use it with the MIN functions or the MAX functions to further refine your results. I have also used it with the AVERAGE function. Want to dig deeper into using IF with other functions? Here are a few visual examples:
Using IF together with the SUM Function
You can use the SUM function with IF statements with multiple conditions to add values that meet specific criteria. For example, we can show how each person performed in both months in sales with the following formula:
=IF(SUM(C2:D2)>29000, "Good," IF(SUM(C2:D2)>25000, "Satisfactory," "Poor"))
This formula returns “Good” if the sum of the person’s sales is more than 29,000, “Satisfactory” if it’s more than 25,000, and “Poor” if it’s less than that.
Using IF Together with the VLOOKUP Function
The IF function and VLOOKUP together are also pretty useful functions. Usually, if the VLOOKUP cannot find a value it returns an error. However, if we add the IF function, we can decide what the formula should return if the value isn’t found. If you remember the syntax from above, you’ll notice that we’re using the VLOOKUP function in the “value_if_false” spot of the function. That gives us a much more helpful result than leaving the value_if_false spot blank.
For our example, we used the formula:
=IF(ISNA(VLOOKUP(A16, A2:D13, 3,FALSE )), " ", VLOOKUP(E1, A2:D13, 3, FALSE)).
The formula returns blank if the value is not found, as shown in the example above.
Related: How to Convert Excel to Google Sheets
[adthrive-in-post-video-player video-id=”h8M16kaH” upload-date=”2023-10-31T06:00:00.000Z” name=”How to Search If Value Exists in Excel” description=”I’ll walk you through the three most common ways to check whether a value exists in a range in Excel. ” player-type=”default” override-embed=”default”]
Frequently Asked Questions
Here are some of the most common questions about using the IF function in excel with multiple conditions. I’ll try to be as concise as possible here. Please let me know if I missed anything!
Can You Have 3 Conditions in an IF Statement in Excel?
Yes, it is possible to have three or more conditions in an IF statement in Excel by nesting multiple IF functions together. This approach allows you to create complex logical tests and decide based on multiple criteria. You can test additional conditions using each nested IF statement as the value_if_false argument of the preceding IF statement. You could also use the IFS function instead.
How Do You Write an IF Statement in Excel With Multiple Conditions?
To write an IF statement in Excel with multiple conditions, you can use logical operators, such as AND, OR, or nested IF statements.
Here’s how to write the IF statement with the AND function:
=IF(AND(condition1, condition2, condition3), value_if_true, value_if_false)
The same works for the OR logical text. You can also nest multiple IF statements to handle more complex conditions. Each nested IF statement becomes the value_if_false argument of the previous IF statement. Here’s how to write a nested IF statement:
=IF(condition1, value_if_true, IF(condition2, value_if_true, value_if_false))
What Is the Difference Between Nested IF and Multi-Way IF Statements?
The main fundamental difference between the nested IF and multi-way IF statements is in the structure. However, both are used for multiple conditions. The difference between the two is only significant in programming, and you’re unlikely to run into issues with this in Excel.
How do I do multiple IF statements in Excel?
If you have multiple conditions in Excel, you can use multiple IF statements. You can also use the AND function to add conditions to a single statement. You can also use IFS to require multiple conditions from your data. Each of these methods is described in more detail above.
How do you write an IF then ELSE formula in Excel?
If you’re familiar with the logic of most programming languages, you may want to use IF then ELSE style statements. In Microsoft Excel, that’s part of the syntax of the IF function. Just list the “ELSE” condition in the “value_if_false” spot.
Bottom Line
There are many ways to use Excel IF statements with multiple conditions, and we’ve shown you everything you need to know to start building your own formulas in your workbooks.
If you found this guide helpful and would like to support our site, please check out our premium template library. And remember, you can save 50% at checkout using the code SSP.
Related:
