We’ll use a dataset of a company’s Sales Report. It shows the information for Product Code and Monthly Sales in the cell range B4:C10.
We need to determine the Sales Status according to these 3 conditions shown in the image below:
Method 1 – Applying a Nested IF Function with 3 Conditions in Excel
- Insert this formula in cell D5:
=IF(C5>=2500,"Excellent",IF(C5>=2000,"Good",IF(C5>=1000,"Average")))
- Hit Enter.
- Use the Autofill tool and you will get all the status for each sales amount.
Method 2 – Combining IF Function and Excel AND Logic for 3 Conditions
- Insert this formula in cell D5.
=IF(AND(C5>=2500),"Excellent",IF(AND(C5>=2000),"Good",IF(AND(C5>=1000),"Average","")))
- Press Enter.
- Apply the formula for the cell range D6:D10.
Method 3 – Joining the IF Function with OR Logic Based on 3 Conditions
- Select cell D5.
- Insert this formula.
=IF(OR(C5>=2500),"Excellent",IF(OR(C5>=2000),"Good",IF(OR(C5>=1000),"Average","")))
- Hit Enter.
- Use the AutoFill tool in cell range D6:D10.
Method 4 – Applying SUM with Excel IF Statement for 3 Conditions
- Apply this formula in cell D5.
=IF(SUM(C5>=2500),"Excellent",IF(SUM(C5>=2000),"Good","Average"))
- Press Enter to see the first output.
- Apply the AutoFill tool and you will get all the statuses with 3 conditions.
Method 5 – Combining Excel IF and AVERAGE Functions with 3 Conditions=
- Select cell D5.
- Insert this formula into the cell.
=IF(AVERAGE(C5>=2500),"Excellent",IF(AVERAGE(C5>=2000),"Good","Average"))
- Press Enter.
- Apply this formula for cell range D6:D10.
Excel IF Function with 2 Conditions
- We’ll take two conditions: Profit and Loss based on the values >=2,500 and >=1,000, respectively.
- Insert this formula in cell D5.
=IF(AND(C5>=2500,OR(C5>=1000)),"Profit","Loss")
- After this, press Enter and you will see the first status for the value in cell C5.
- Use the FlashFill tool and get the final output based on 2 conditions.
Download the Practice Workbook
Related Articles
- Example of VLOOKUP with Multiple IF Condition in Excel
- How to Use PERCENTILE with Multiple IF Condition in Excel
<< Go Back to Multiple IF Condition in Excel | Excel IF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
With your guidance, every thing seems to be so easy and simple.
Thanks for your feedback. Glad to know that it helps you.
Thanks for teaching formula with easy learning example.
This is helping people like me in profession to making reports and getting work faster.
Thanks for your valuable feedback.
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 ” “
Thanks, LOUIS for reaching out to us. Please drop your excel file and queries at [email protected]
This is Aung, one of the writers of ExcelDemy. I’d like to help to solve your problem, but I didn’t exactly understand your criteria. I hope you elaborate a bit further.
Thanks.
Thank you for your nice training. it will help me a lot
Welcome ASIM, glad we could help you.