Simplifying Data Analysis: Mastering Excel's IF Statements with Multiple Conditions

The IF function is really versatile and can be used in all sorts of ways to make logical comparisons and return different results based on the outcome. 

=IF(B2>10,"Above average", "Below average")

This formula checks if the value in cell B2 is greater than 10. If it is, then it returns the text "Above average". If it's not, then it returns the text "Below average".

So, for instance, if cell B2 contains the value 12, then the formula will return "Above average". But if cell B2 contains the value 8, then it will return "Below average".

Common Problems Faced when working with IF statement on Excel 

However, you may encounter some problems when working with Excel IF statements. The problems are given below: 

IF Function- Single Condition 

To create the logical test in your formula, you have the flexibility to use various components such as cell references, operators, constants, and even other functions. You can utilize operators such as greater than (>), less than (<), and equal to (=) to produce either True or False values.

The example given below demonstrates how to use the IF function to determine whether a student has passed or failed based on their exam score.

Here is the screenshot with the sample example:

Formula Explanation 

‍

How to use Excel IF Function with multiple criteria? 

The tutorial shows how to create multiple IF statements in Excel with AND as well as OR logic. Also, you will learn how to use IF together with other Excel functions.

To use the IF formula with multiple criteria, you can employ either the AND or OR function based on your desired logical operation. The AND function returns TRUE only when all the specified conditions are met, while the OR function returns TRUE when any one of the conditions is met. Here are some examples of real-life formulas to further illustrate the concept.

Excel IF statement with multiple conditions (using AND)

The Excel IF function with two or more conditions follows a generic formula:

=IF(AND(condition1, condition2, ...), value_if_true, value_if_false). What this means is that “If condition 1 is true AND condition 2 is true, return value_if_true; else return value_if_false.” 

The example given below demonstrates how to use the IF/AND formula in Excel to check if a student passed an exam based on their scores in two tests, connecting the subtopic of Excel IF statements with multiple conditions using AND.

Here, we give you an example to give a proper explanation: 

One example of using the AND function in an exam is to check if a student has passed the final exam based on their scores in two tests. If the scores are listed in columns B and C, the formula for the logical test could be: AND(B2>50, C2>50).

The IF/AND formula in Excel is simple and effective. 

In the formula "=IF(AND(B2>50, C2>50), "Pass", "Fail")", if both conditions are met, the formula will return "Pass", and if either or both conditions are not met, it will return "Fail". 

The screenshot below provides evidence that our formula works correctly:

Formula Explanation 

‍

Also, as you can see below, as the cell placement changes, the formula is adjusted accordingly to get the correct results: 

Formula Explanation 

‍

Or 

Formula Explanation 

‍

You can also utilize the IF function in Excel with multiple text conditions. For example, if you want to display "Excellent" if both B2 and C2 are "A", and "Needs Improvement" if any of the grades are "D" or "F", you can use this formula:

=IF(AND(B2="A", C2="A"), "Excellent", IF(OR(B2="D", B2="F", C2="D", C2="F"), "Needs Improvement", "Excellent"))

Excel IF function with OR logic

To do one thing if any condition is met, otherwise do something else, you can use the combination of the IF and OR functions in Excel.

The formula structure is:

IF(OR(condition1, condition2, …), value_if_true, value_if_false)

The difference from the IF/AND formula is that Excel returns TRUE if any of the specified conditions is true.

Now, the example given below showcases how to use the IF/OR formula in Excel to determine if a student has passed a test based on two different criteria, emphasizing the usage of the OR logic with the Excel IF function.

For example, if you want to check if a student has passed a test based on two different criteria, you can use the following formula:

=IF(OR(B2>50, C2>50), "Pass", "Fail")

Formula Explanation 

‍

This formula checks if either the score in cell B2 or the score in cell C2 is greater than 50. If any of these conditions are true, the formula returns "Pass". If not, it returns "Fail".

Also, if you're creating a multiple IF statement with text and testing a value in one cell with the OR logic, you can build a more compact formula using an array constant in Excel.

For example, suppose you want to mark a sale as "closed" if cell B2 is either "delivered" or "paid". You can use the following formula:

=IF(OR(B2={"delivered", "paid"}), "Closed", "")

This formula checks if the value in cell B2 is either "delivered" or "paid" using the OR function and an array constant. If the condition is true, the formula returns "Closed". If not, it returns an empty string (i.e., "").

Formula Explanation 

‍

Using an array constant in this way can make your formulas more concise and easier to read, especially when dealing with multiple criteria.

IF with multiple AND & OR statements

By combining logical operators, such as AND and OR, in the IF formula, you can create complex logical tests to make decisions and generate results accordingly. Using the IF function in Excel with multiple AND and OR statements helps to evaluate multiple conditions and determine different outcomes based on the combination of those conditions.

Step 1: Express each condition as an AND statement:

Condition 1: exam1>50 and exam2>50 => AND(B2>50, C2>50)

Condition 2: exam1>40 and exam2>60 => AND(B2>40, C2>60)

Step 2: Combine the above two AND statements using the OR function:

Final Formula: OR(AND(B2>50, C2>50), AND(B2>40, C2>60))

This formula checks if either Condition 1 or Condition 2 is met, and if either is true, it returns a value of TRUE, indicating that the final exam has been passed. 

The example given here demonstrates the usage of the IF function in Excel with multiple AND and OR statements to determine the outcome of a final exam based on various conditions, showcasing the combination of logical operators to achieve the desired result.

Formula Explanation 

‍

If both conditions are false, the formula returns a value of FALSE, indicating that the final exam has not been passed.

Formula Explanation 

‍

Now, using the OR function for the logical test of IF: 

=IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60)), "Good", "Fail")

This formula checks if either Condition 1 or Condition 2 is met. If either is true, it returns a value of "Good", indicating that the final exam has been passed.

Formula Explanation 

‍

 If both conditions are false, the formula returns a value of "Fail", indicating that the final exam has not been passed.

Formula Explanation 

‍

Conclusion 

In conclusion, the IF function in Excel, when combined with the AND and OR functions, allows for the evaluation of multiple conditions and returning different results based on those conditions. By using the IF function with AND, you can ensure that all conditions must be met for a specific outcome. Conversely, by using the IF function with OR, you can check if any of the conditions are met for a particular result. 

This versatility enables tasks such as grading, categorizing data, and automating decision-making in spreadsheets. Pay attention to syntax and structure, including proper nesting and value_if_true/value_if_false arguments, to ensure accurate results. Mastering these concepts empowers you to handle various scenarios efficiently and effectively in Excel.

Meanwhile, keySkillset is a platform that combines all modalities of learning from simulation to video/ audio, slides and one-to-one tutorials on programs from Excel to Python, SQL etc.

‍

‍