IF Function with 3 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. Here we will see the examples of multiple if functions along with IF functions with AND & OR logic in Excel.

Multiple IF Statements in Excel (3 IF Functions)

Here, we will use three If functions in one formula to determine the sales status of different products. Let`s say we have some products which are listed as product code in one column. In another column, the monthly sales amount is given. The Product list along with the price is given below.

We will be determining the sales status of these products based on some conditions. The conditions are listed below.

StatusValue
Excellent>=2500
Good>=2000
Average>=1000
Poor>=500

Based on these conditions we will use multiple if functions in column C to determine the sales condition of the products. The formula we will be using is: =IF(B2>=2500,"Excellent",IF(B2>=2000,"Good",IF(B2>1000,"Average","Poor ")))

In C2, write down the above formula and press enter. You will see the result Excellent will show up in cell C2 as the value 3000 is greater than 2500.

Now, after dragging the formulated cell downwards you will get to see the result for the whole column.

IF functions with AND & OR logic in Excel

We will be working on an interesting example to show you the if function with 3 conditions. Here we will be using the AND & OR function nesting in the IF function.

The experiment will be selecting a bowler based on his performance. Let`s say there are total 8 bowlers who played a different number of matches. Based on their performances we will be deciding who has good records and who has the bad records. The conditions are given below.

  • He has to play at least 7 matches. (This needs to be fulfilled)
  • He has to take at least 9 wickets.
  • He needs to have an economy rate less than 6.

Here, The Bowler must meet the 1st condition. After being eligible based on the 1st condition, either the 2nd or 3rd condition needs to be met up. The table is given below.

Here we will be using the below formula. =IF(AND(B2>=7,OR(C2>9,D2<6)), "Good Record","Bad Record")

First put this formula in cell E2 and drag it down to use this formula for the rest of the cells. You will get to see the below result.

Download The Working File

Conclusion

Whenever you want to evaluate a result based on some conditions you may need to use the multiple if functions or if function with multiple conditions. Hope by reading this article you will get to know how if statement in excel with multiple conditions work.


Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.

5 Comments
  1. Reply
    Ramesh Chandra September 4, 2018 at 12:33 PM

    With your guidance, every thing seems to be so easy and simple.

    • Reply
      Kawser September 4, 2018 at 6:26 PM

      Thanks for your feedback. Glad to know that it helps you.

  2. Reply
    Shahnawaz Ansari September 6, 2018 at 11:17 AM

    Thanks for teaching formula with easy learning example.
    This is helping people like me in profession to making reports and getting work faster.

    • Reply
      Kawser September 6, 2018 at 12:23 PM

      Thanks for your valuable feedback.

  3. Reply
    louis April 18, 2019 at 9:12 AM

    hi i want to create a formula that meet below condition, could you help, thanks:
    A equal B equal and it have to be more than 0 (which is not blank) then Matched if not the unmatched
    if Both A and B is blank then show blank ” “

    Leave a reply