How to Use Multiple IF Condition in Excel (3 Examples)

In this tutorial, we will demonstrate different examples of how to use multiple IF condition in excel. However, we may need to analyze multiple conditions at the same time for effective data analysis. So, we will have to apply multiple IF functions in one formula to meet all the conditions at the same time.


Download Practice Workbook

You can download the practice workbook from here.


3 Examples to Use Multiple IF Condition in Excel

Throughout this tutorial, we will explain 3 different examples to use multiple IF conditions in excel. To illustrate all the examples we will use unique datasets. That dataset will help you to visualize the output.


1. Use Multiple IF Conditions in Nested Form in Excel

First and foremost, we will apply a nested formula to use multiple IF conditions in excel. In the following dataset, we will set the Status of Grades for different students based on their Total marks. We will insert a formula that will show grades for a specific range of marks.

Use Multiple IF Conditions in Nested Form in Excel

Let’s see the steps to perform this action.

STEPS:

  • To begin with, select cell F6.
  • In addition, type the following formula in that cell:
=IF(E6>160,"A",IF(E6>=150,"B",IF(E6>140,"C",IF(E6>130,"D",IF(E6<130,"F")))))
  • Press Enter.
  • The above actions return the value of Grades A in cell F6.

Use Multiple IF Conditions in Nested Form in Excel

  • After that, drag the Fill Handle tool from cell F6 to F10.
  • Lastly, we can see the grades for all students in the following image.

Here, The formula that we used returns grade value A if total marks are greater than or equal to 160, returns grade value B if the total mark is greater than or equal to 150, returns grade value C if the total mark is greater than or equal to 140, returns grade value D if the total mark is greater than or equal to 130, returns grade value F if the total mark is smaller than 130.

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


2.  Multiple IF Condition in Excel with Logical Test

2.1 Apply IF Condition with AND Logic

In this method, we will apply multiple IF conditions with the AND function. In the following dataset, we will insert the IF function with AND function. The formula will return the value Pass if a student obtains marks greater than or equal to 40 in both subjects. So, to return the value Pass it is necessary to meet both conditions.

Apply IF Conditions with AND Logic

Let’s see how we can apply AND function in this method.

STEPS:

  • First, select cell E6.
  • Next, insert the following formula in that cell:
=IF((AND(C6>=40, D6>=40)), "Pass", "Fail")
  • Hit Enter.
  • The above action returns the output value Pass in cell E6.

Apply IF Conditions with AND Logic

  • Then, drag the Fill Handle tool from cell E6 to E10.
  • Finally, we get results like the following image. In the image below we can see the Pass/Fail status of each student.

Here, the formula returns Pass if both conditions are true otherwise it will return Fail.

Read More: IF with AND in an Excel Formula (7 Examples)


2.2 OR Logic with IF Condition

In this example, we will apply the OR function with multiple IF conditions in Excel. In the following dataset, we will figure out the Pass/ Fail status of each student. One student will fail if he/she gets less than or equal to 40 marks in any one subject. So, we have to develop a formula where it will give output if only one condition is satisfied.

OR Logic with IF Condition

Just follow the below steps to do this example.

STEPS:

  • To begin with, select cell E6.
  • In addition, input the following formula in that cell:
=IF((OR(C6<=40, D6<=40)), "Fail", "Pass")
  • Press Enter.
  • Furthermore, the above commands give us the output value in cell E6.

OR Logic with IF Condition

  • Moreover, drag the Fill Handle tool from cell E6 to E10.
  • Lastly, we obtain results similar to the image below. Each student’s Pass/Fail status is represented in the figure below.

Here, the formula that we used returns Pass if any given condition is true otherwise it will return Fail.

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


2.3 Insert Both AND & OR Logic with Multiple IF Condition

In this example, we will use multiple IF conditions in excel with AND & OR functions. Sometimes we might need several conditions on our dataset. Then, we have to apply both AND & OR functions to use multiple IF conditions. In the following dataset, we will apply multiple IF conditions using AND & OR functions.

Insert Both AND & OR Logic with Multiple IF Conditions

So, let’s see the steps to do this.

STEPS:

  • In the beginning, select cell E6.
  • Next, type the following formula in that cell:
=IF(OR(AND(C6>=40, D6>=50), AND(C6>=35, D6>=45)), "Pass", "Fail")
  • Now, hit Enter.
  • Then, the above commands return the value Pass in cell E6.

Insert Both AND & OR Logic with Multiple IF Conditions

  • After that, drag the Fill Handle tool from cell E6 to E10.
  • In the end, we can see the result similar to the following image.

Here, the formula returns Pass if the value in cells C6 & D6 is respectively >=40 & >=50 or it will give the same result if the value in cells C6 & D6 is respectively >=35 & >=45. Any value that doesn’t meet the above conditions will return the value Fail.

Read More: Excel IF Function with 3 Conditions


Similar Readings


3. Apply Multiple IF Conditions with Different Excel Functions

Until now, we have seen how to use multiple IF conditions in excel with AND/OR functions. In this example, we will learn how we can use multiple IF conditions with another function. In the following dataset, we have marks of 3 students in five subjects. We will set the status of students based on total marks. To do this example we will use the SUM function.

Apply Multiple IF Conditions with Different Excel Functions

Just follow the below steps to perform this:

STEPS:

  • Firstly, select cell C11.
  • Secondly, insert the following formula in that cell:
=IF(SUM(C6:C10)>=350, "Good", IF(SUM(C6:C10)>=300, "Satisfactory", "Poor "))
  • Press Enter.
  • So, we get the value Good in cell C11.

Apply Multiple IF Conditions with Different Excel Functions

  • Thirdly, drag the Fill Handle tool horizontally from cell C11 to E11.
  • As a result, we can see the result in the image below.

Here, the formula that we used returns the value Good if the value of SUM(C6:C10) is greater than or equal to 350. It returns Satisfactory if the value of SUM(C6:C10) is greater than or equal to 300. If the value of SUM(C6:C10) is smaller than 300 the formula will return the value Poor.

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


Conclusion

In conclusion, this tutorial shows you 3 different examples of how to use multiple IF condition in excel. Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment below. Our team will try to reply to you as soon as possible. Keep an eye out for more creative Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo