Multiple IFs in Excel (2023)

Multiple IF Conditions in Excel

The multiple IF conditions in Excel are IF statements contained within another IF statement. They are used to test multiple conditions simultaneously and return distinct values. The additional IF statements can be included in the “value if true” and “value if false” arguments of a standard IF formula.

For example, suppose we have a dataset of students’ scores from B1:B12. We need to grade the students according to their scores. Then, using the IF condition, we can manage the multiple conditions. In this example, we can insert the nested IF formula in cell D1 to assign a grade to a score. We can grade total score as “A,” “B,” “C,” “D,” and “F.” A score would be “F” if it is greater than or equal to 30, “D” if it is greater than 60, and “C” if it is greater than or equal to 70, and “A,” “B” if the score is less than 95. We can insert the formula in D1 with 5 separate IF functions:

=IF(B1>30,”F”,IF(B1>60,”D”,IF(B1>70,”C”,IF(C5>80,”B”,”A”))))

Table of contents
  • Multiple IF Condition in Excel
    • Explanation
    • Examples
      • Example #1
      • Example #2
      • Example #3
      • Example #4
    • Things to Remember
    • Recommended Articles

Explanation

The IF formula is used when we wish to test a condition and return one value if the condition is met and another value if it is not met.

Each subsequent IF formula is incorporated into the “value_if_false” argument of the previous IF. So, the nested IF excelNested IF ExcelIn Excel, nested if function means using another logical or conditional function with the if function to test multiple conditions. For example, if there are two conditions to be tested, we can use the logical functions AND or OR depending on the situation, or we can use the other conditional functions to test even more ifs inside a single if.read more formula works as follows:

Multiple IFs in Excel (1)

Syntax

IF (condition1, result1, IF (condition2, result2, IF (condition3, result3,………..)))

Examples

You can download this Multiple Ifs Excel Template here –Multiple Ifs Excel Template

(Video) Learn Excel - Multiple Conditions in IF - Podcast 2025

Example #1

Suppose we wish to find how a student scores in an exam. There are two exam scores of a student, and we define the total score (sum of the two scores) as “Good,” “Average,” and “Bad.” A score would be “Good” if it is greater than or equal to 60, ‘Average’ if it is between 40 and 60, and ‘Bad’ if it is less than or equal to 40.

Let us say the first score is stored in column B, the second in column C.

Multiple IFs in Excel (2)

The following formula tells Excel to return “Good,” “Average,” or “Bad”:

=IF(D2>=60,”Good”,IF(D2>40,”Average”,”Bad”))

Multiple IFs in Excel (3)

This formula returns the result as given below:

Multiple IFs in Excel (4)

Drag the formula to get results for the rest of the cells.

We can see that one multiple IF function is sufficient in this case as we need to get only 3 results.

Multiple IFs in Excel (5)

We can see that one multiple IF function is sufficient in this case as we need to get only 3 results.

(Video) Excel IF Formula: Simple to Advanced (multiple criteria, nested IF, AND, OR functions)

Example #2

We want to test one more condition in the above examples: the total score of 70 and above is categorized as “Excellent.”

=IF(D2>=70,”Excellent”,IF(D2>=60,”Good”,IF(D2>40,”Average”,”Bad”)))

Multiple IFs in Excel (6)

This formula returns the result as given below:

Multiple IFs in Excel (7)

Excellent: >=70

Good: Between 60 & 69

Average: Between 41 & 59

Bad: <=40

Drag the formula to get results for the rest of the cells.

Multiple IFs in Excel (8)

We can add several “IF” conditions if required similarly.

(Video) Properly Use Excel IFS Function

Example #3

Suppose we wish to test a few sets of different conditions. In that case, those conditions can be expressed using logical OR and AND, nesting the functions inside IF statements and then nesting the IF statements into each other.

For instance, if we have two columns containing the number of targets made by an employee in 2 quarters: Q1 and Q2. Then, we wish to calculate the performance bonus of the employee based on a higher target number.

Multiple IFs in Excel (9)

We can make a formula with the logic:

  1. If either Q1 or Q2 targets are greater than 70, then the employee gets a 10% bonus,
  2. If either of them is greater than 60, then the employee receives a 7% bonus,
  3. If either of them is greater than 50, then the employee gets a 5% bonus,
  4. If either is greater than 40, then the employee receives a 3% bonus. Else, no bonus.

So, we first write a few OR statements like (B2>=70,C2>=70), and then nest them into logical tests of IF functions as follows:

=IF(OR(B2>=70,C2>=70),10%,IF(OR(B2>=60,C2>=60),7%, IF(OR(B2>=50,C2>=50),5%, IF(OR(B2>=40,C2>=40),3%,””))))

Multiple IFs in Excel (10)

This formula returns the result as given below:

Multiple IFs in Excel (11)

Next, drag the formula to get the results of the rest of the cells.

Multiple IFs in Excel (12)

Example #4

Now, let us say we want to test one more condition in the above example:

  1. If both Q1 and Q2 targets are greater than 70, then the employee gets a 10% bonus
  2. if both of them are greater than 60, then the employee receives a 7% bonus
  3. if both of them are greater than 50, then the employee gets a 5% bonus
  4. if both of them are greater than 40, then the employee receives a 3% bonus
  5. Else, no bonus.

So, we first write a few AND statements like (B2>=70,C2>=70), and then nest them: tests of IF functions as follows:

(Video) How to use the IFS function in Excel

=IF(AND(B2>=70,C2>=70),10%,IF(AND(B2>=60,C2>=60),7%, IF(AND(B2>=50,C2>=50),5%, IF(AND(B2>=40,C2>=40),3%,””))))

Multiple IFs in Excel (13)

This formula returns the result as given below:

Multiple IFs in Excel (14)

Next, drag the formula to get results for the rest of the cells.

Multiple IFs in Excel (15)

Things to Remember

  • The multiple IF function evaluates the logical tests in the order they appear in a formula. So, for example, as soon as one condition evaluates to be “True,” the following conditions are not tested.
    • For instance, if we consider the second example discussed above, the multiple IF condition in Excel evaluates the first logical test (D2>=70) and returns “Excellent” because the condition is “True” in the below formula:

=IF(D2>=70,”Excellent”,IF(D2>=60,,”Good”,IF(D2>40,”Average”,”Bad”))

Now, if we reverse the order of IF functions in Excel as follows:

=IF(D2>40,”Average”,IF(D2>=60,,”Good”,IF(D2>=70,”Excellent”,”Bad”))

In this case, the formula tests the first condition. Since 85 is greater than or equal to 70, a result of this condition is also “True,” so the formula would return “Average” instead of “Excellent” without testing the following conditions.

Correct Order

Multiple IFs in Excel (16)

Incorrect Order

(Video) All 3 Excel IF Statements + how to AVOID PERFORMANCE ISSUES!

Multiple IFs in Excel (17)

Note: Changing the order of the IF function in Excel would change the result.

  • Evaluate the formula logic– To see the step-by-step evaluation of multiple IF conditions, we can use the ‘Evaluate Formula’ feature in excel on the “Formula” tab in the “Formula Auditing” group. Clicking the “Evaluate” button will show all the steps in the evaluation process.
  • For instance, in the second example, the evaluation of the first logical testLogical TestA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more of multiple IF formulas will go as D2>=70; 85>=70; True; Excellent.
Multiple IFs in Excel (18)
  • Balancing the parentheses: If the parentheses do not match in terms of number and order, then the multiple IF formula would not work.
    • If we have more than one set of parentheses, the parentheses pairs are shaded in different colors so that the opening parentheses match the closing ones.
    • Also, on closing the parenthesis, the matching pair is highlighted.
  • Numbers and Text should be treated differently: The text should always be enclosed in double quotes in the multiple IF formula.
  • Multiple IF’s can often become troublesome: Managing many true and false conditions and closing brackets in one statement becomes difficult. Therefore, it is always good to use other tools like IF function or VLOOKUP in case Multiple IF’sVLOOKUP In Case Multiple IF'sSometimes while working with data, when we match the data to the reference Vlookup, it finds the first value and does not look for the next value. However, for a second result, to use Vlookup with multiple criteria, we need to use other functions with it.read more are difficult to maintain in Excel.

Recommended Articles

This article is a guide to Multiple IF Conditions in Excel. We discuss using multiple IF conditions, practical examples, and a downloadable Excel template. You may also learn more about Excel from the following articles: –

  • IF OR in VBAIF OR In VBAIF OR is not a single statement; it is a pair of logical functions used together in VBA when we have more than one criteriato check, and when we use the if statement, we receive the true result if either of the criteriais met.read more
  • COUNTIF in ExcelCOUNTIF In ExcelThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump”read more
  • IFERROR Excel Function – ExamplesIFERROR Excel Function - ExamplesThe IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error.read more
  • SUMIF Excel FunctionSUMIF Excel FunctionThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12.read more

FAQs

How to do multiple ifs in Excel? ›

Another way to get an Excel IF to test multiple conditions is by using an array formula. To complete an array formula correctly, press the Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.

Can you do 3 IFS in Excel? ›

Excel allows a max of 7 nested if statements. If we wanted to expand our list of possible statuses, we could add only one more condition and one more status. But fortunately we can add more using a different function.

Can you do nested ifs in Excel? ›

Nested IF in Excel with OR statements

By using the OR function you can check two or more different conditions in the logical test of each IF function and return TRUE if any (at least one) of the OR arguments evaluates to TRUE.

What can I use instead of multiple ifs? ›

To test multiple conditions and return different values based on the results of those tests, you can use the CHOOSE function instead of nested IFs.

How do I use multiple ifs with Vlookup? ›

Can you combine the IF function and the VLOOKUP function? Yes, you can, in fact, it is the easiest way to VLOOKUP using two or more conditions. To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once.

Can you have more than 3 conditional formats in Excel? ›

You can apply multiple conditional formats to the same cells.

Can Sumifs have 3 criteria? ›

As you see, the syntax of the Excel SUMIF function allows for one condition only.

Is ifs the same as nested IF? ›

The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.

How do I insert an IF function with nested? ›

Use nested functions in a formula
  1. Click the cell in which you want to enter the formula.
  2. To start the formula with the function, click Insert Function on the formula bar . ...
  3. In the Or select a category box, select All. ...
  4. To enter another function as an argument, enter the function in the argument box that you want.

Should you avoid nested ifs? ›

Why This is Bad. Deeply nested conditionals make it just about impossible to tell what code will run, or when. The big problem with nested conditionals is that they muddy up code's control flow: in other words, they make it just about impossible to tell what code will run, or when.

Why is VLOOKUP better than nested ifs? ›

That's because VLOOKUP is using the commission table on the worksheet directly. This makes the formula much shorter and easier to read. More importantly, this means I can just edit the table if I want to change the commission structure.

Can you use Xlookup with two conditions? ›

One of the key new features XLOOKUP brings to the table is the ability to lookup using multiple criteria (without complex array formulas – arrays are now dynamic!).

Can you use Xlookup for multiple values? ›

One more amazing feature of XLOOKUP is its ability to return more than one value relating to the same match.

Can you have 2 conditional formats in one cell? ›

You can combine multiple conditional formats overlapping cells/rows. (Excel's just a little picky that you do it just right!) Always use New Rule as opposed to using the built-in rules.

What is the maximum number of conditional formats that can be added at once? ›

For complex formatting, however, there is one drawback for most Excel users: The conditional formatting feature only seems to allow up to three conditions to be defined.

Can I Adding 2 Sumif formulas? ›

The SUMIF function in Excel is designed for only one criterion or condition. When we need to sum values based on multiple criteria, we can add two or more SUMIF functions, or we use a combination of SUM and SUMIF functions.

Is Sumifs better than Sumif? ›

The only difference between Excel SUMIFS & SUMIF functions is that SUMIFs can check for multiple criteria at once, while SUMIF can check for one criterion at a time. The SUMIF formula returns the sum of cells based on one criterion (a result that matches one condition).

How do I use Sumifs with multiple criteria in different columns? ›

To sum cells that match multiple criteria, you normally use the SUMIFS function. The problem is that, just like its single-criterion counterpart, SUMIFS doesn't support a multi-column sum range. To overcome this, we write a few SUMIFS, one per each column in the sum range: SUM(SUMIFS(…), SUMIFS(…), SUMIFS(…))

How do you do multiple functions? ›

Multiplication of Functions

To multiply a function by another function, multiply their outputs. For example, if f (x) = 2x and g(x) = x + 1, then fg(3) = f (3)×g(3) = 6×4 = 24.

How do I combine 3 cell values in Excel? ›

Merge cells
  1. Click the first cell and press Shift while you click the last cell in the range you want to merge. Important: Make sure only one of the cells in the range has data.
  2. Click Home > Merge & Center.

Can we use multiple ifs in algorithm? ›

You can use multiple else if but each of them must have opening and closing curly braces {} . You can replace if with switch statement which is simpler but only for comparing same variable.

What is a nested if statement? ›

Nested if is a decision-making statement that works similar to other decision-making statements such as if, else, if..else, etc. It executes a block of code if the condition written within the if statement is true. However, in the nested-if statement, the block of code is placed inside another if block.

How many nested ifs is too much? ›

Remarks. While Excel will allow you to nest up to 64 different IF functions, it's not at all advisable to do so.

Is switch more efficient than a set of nested ifs? ›

Prerequisite – Switch Statement, Decision making(if else) A switch statement is usually more efficient than a set of nested ifs. Deciding whether to use if-then-else statements or a switch statement is based on readability and the expression that the statement is testing.

What are the disadvantages of nested if-else statement? ›

Disadvantages: if-else statements increase the number of code paths to be tested. If there are a lot of if statements the code sometimes becomes unreadable and complex, in such cases we use Switch case statement.

How many else ifs can I use? ›

You can have as many else if statements as necessary. In the case of many else if statements, the switch statement might be preferred for readability. As an example of multiple else if statements, we can create a grading app that will output a letter grade based on a score out of 100.

How to avoid multiple IF statements in JavaScript? ›

  1. Ternary operator. Most widely used method to avoid the use of if - else statements. ...
  2. Short circuit (Using && , || operators) This method evaluate the expression using '&&' and '||' operators. ...
  3. Object look-ups. ...
  4. Early returns and less nesting. ...
  5. Function delegation.
Dec 4, 2020

What can I use instead of nested IF statements in C++? ›

Some alternatives to the if-else statement in C++ include loops, the switch statement, and structuring your program to not require branching.

Can you put multiple IF functions in one cell? ›

As a worksheet function, the IF function can be entered as part of a formula in a cell of a worksheet. It is possible to nest multiple IF functions within one Excel formula. You can nest up to 7 IF functions to create a complex IF THEN ELSE statement.

How do I stop nested if conditions? ›

Avoid using nested if-else statements. Keep the code linear and straightforward. Utilize creating functions/methods. Compare it when we try to use an if-else statement that is nested and that does not utilize the power of the return statement, We get this (Code 1.4).

Which is more efficient than nested IF? ›

Prerequisite – Switch Statement, Decision making(if else) A switch statement is usually more efficient than a set of nested ifs.

Is else if better than nested IF? ›

There's not really a difference. The if, else if, else conditional is actually the same as the nested one with one of the {} enclosures removed.

Videos

1. Nested If Function in Excel : How to write If Function with multiple conditions
(Excel Destination)
2. IF Formula in Excel With Multiple Conditions | If Else Statement in Excel | Nested IF And Or
(Swift Learn)
3. Excel: IF Function MULTIPLE CRITERIA | IF ELSE Function | Nested IF Function
(Chester Tugwell)
4. IF AND OR Formula in Excel with MULTIPLE CONDITIONS
(Presentation Mastery)
5. IFS() Function Excel - Easy Nested IF Statements in Excel
(TeachExcel)
6. IFS Excel Function: The Easier Way to Do Multiple Conditions
(THE EXCEL ZONE - Microsoft Excel Tutorial)
Top Articles
Latest Posts
Article information

Author: Manual Maggio

Last Updated: 02/22/2023

Views: 5357

Rating: 4.9 / 5 (69 voted)

Reviews: 92% of readers found this page helpful

Author information

Name: Manual Maggio

Birthday: 1998-01-20

Address: 359 Kelvin Stream, Lake Eldonview, MT 33517-1242

Phone: +577037762465

Job: Product Hospitality Supervisor

Hobby: Gardening, Web surfing, Video gaming, Amateur radio, Flag Football, Reading, Table tennis

Introduction: My name is Manual Maggio, I am a thankful, tender, adventurous, delightful, fantastic, proud, graceful person who loves writing and wants to share my knowledge and understanding with you.