Excel IF Function with 3 Conditions (5 Logical Tests)

When you are performing complicated and powerful data analysis, you need to justify various conditions at a single point in time. In Microsoft Excel, the IF function acts as a powerful tool to work on conditions. In this tutorial, we will be working on the nested IF function in Excel. We will analyze the IF function with 3 conditions in Excel based on 5 logical tests.

Download Practice Workbook

Download this practice file and try the methods by yourself.

5 Logical Tests on Excel IF Function with 3 Conditions

To describe the process, here is a dataset of a company’s Sales Report. It shows the information of Product Code and Monthly Sales in cell range B4:C10.

We need to determine the Sales Status according to these 3 conditions shown in the image below:

Now, let us apply the following logical tests to identify the status of the 6 products in the dataset.

1. Nested IF Function with 3 Conditions

In Excel, we can nest multiple IF functions at the same time to perform complicated calculations.

In this article, we will apply the nested IF function for 3 conditions. Let’s follow the process.

• First, insert this formula in cell D5.

• Next, hit Enter.
• After that, you will see the first status based on the conditions applied.

Here, we used the IF function to apply a logical comparison between the conditions for the selected cell C5.

• Following, use the Autofill tool and you will get all the status for each sales amount.

Read More: VBA IF Statement with Multiple Conditions in Excel (8 Methods)

2. IF Function with AND Logic for 3 Conditions in Excel

In this section, we will apply the IF function incorporating the AND function for the logical test. Follow the steps below.

• First, insert this formula in cell D5.

• Next, press Enter and you will see the first output.

Here, we combined the IF and AND functions to compare each condition with their text individually and return the value if the conditions do not meet the cell value in C5. In the end, we inserted the Empty String (“”) to omit empty cells if any.

• Lastly, apply this formula for cell range D6:D10 and see the final result.

Read More: Excel VBA: Combining If with And for Multiple Conditions

3. Excel IF Function with OR Logic Based on 3 Conditions

The combination of IF and OR functions is also a very powerful tool to run the logic test with 3 conditions. Let’s see how it works.

• In the beginning, select cell D5.
• Here, insert this formula.

• After that, hit Enter.
• Lastly, use the AutoFill tool in cell range D6:D10.
• Finally, you will see the output as shown below:

Here, the IF and OR function benefits comparison within 3 conditions. Therefore, it determines the conditions Excellent, Good and Average according to their values.

Read More: Excel VBA: Combined If and Or (3 Examples)

4. Excel IF Statement with SUM Function for 3 Conditions

If your dataset does not work on any of the logical tests above, you can go for the SUM function incorporated in the IF statement. It will successfully work for the 3 conditions.

• First, apply this formula in cell D5.

• After this, press Enter to see the first output.

Here, the combination of the IF function compares each condition against the value in cell C5. Following this, the SUM function calculates the value based on the condition to determine whether it is true or false.

• Lastly, apply the AutoFill tool and you will get all the statuses with 3 conditions.

Read More: Excel VBA: If Then Else Statement with Multiple Conditions (5 Examples)

5. Combine IF & AVERAGE Functions with 3 Conditions in Excel

The AVERAGE function is also helpful if you have a different set of data strings. It combines with the IF function for comparing between conditions. Let’s see the process below.

• In the beginning, select cell D5.
• Then, insert this formula into the cell.

• After that, press Enter.
• Lastly, apply this formula for cell range D6:D10.
• Finally, you will see the output as shown below.

Here, we used the IF function for comparing the 3 conditions. Then, applied the AVERAGE function to return the average value (if any) for the selected cell.

Read More: How to Apply Excel IF Statement with Multiple Conditions in Range

Excel IF Function with 2 Conditions

Here is an additional tip for you, if you are working with 2 conditions. Let’s see how the IF function works in this case.

• In the beginning, let us take two conditions: Profit and Loss based on the values >=2500 and >=1000 respectively.

• Then, insert this formula in cell D5.

• After this, press Enter and you will see the first status for the value in cell C5.

Here, the IF, AND & OR functions are combined to determine the conditions of Profit and Loss for cell C5 based on the conditional values.

• Lastly, use the FlashFill tool and get the final output based on 2 conditions.

Read More: How to Use IF Function with Multiple Conditions in Excel

Things to Remember

• It is mandatory to follow the order of the conditions in the formula that you initially set up. Otherwise, it will show the wrong value.
• Make sure to balance every Parenthesis according to the numbers and order inside the formula to get a proper result.
• If the conditions are placed in Text format, they must be enclosed within double quotes.

Conclusion

Concluding this article with the hope that it was a helpful one on Excel IF function with 3 conditions based on 5 logical tests. I also tried to cover the process in 2 conditions. Let us know your insightful suggestions in the comment box. Follow ExcelDemy for more blogs.

Related Articles

• How to Use Multiple If Conditions in Excel for Aging (5 Methods)
• Example of VLOOKUP with Multiple IF Condition in Excel (9 Criteria)
• How to Use PERCENTILE with Multiple IF Condition in Excel (3 Examples)
• Excel IF between Multiple Ranges (4 Approaches)
• How to Use Multiple IF Statements with Text in Excel (6 Quick Methods)