# How to Use IF Function with OR and AND Statement in Excel

### Example 1 – Utilize IF Function with OR and AND Statement in Excel to Analyze Financial Data

Steps:

• Enter the following formula in cell G5.
`=IF(OR(C5>\$C\$12,D5>\$D\$12,AND(E5=\$E\$12,F5>\$F\$12)),"Flag","OK")`
• Press the Enter or Tab keys to see the output Flag.
• This represents the conditions are TRUE for the Production department.

• Use the AutoFill tool to obtain the rest of the outputs.

Formula Breakdown
• OR(C5>\$C\$12,D5>\$D\$12 indicates the statement is TRUE either the Budget is > 200,000 or YTD Spent is > 100,000.
• AND(E5=\$E\$12,F5>\$F\$12) represents the statement is TRUE if E5 is + Yes and Overrun% > 20% are true.
• IF(OR(C5>\$C\$12, D5>\$D\$12, AND(E5=\$E\$12, F5>\$F\$12)),” Flag”, “OK”) commands if the conditions are TRUE then display Flag otherwise OK.

Read More: How to Use IF with AND Function in Excel (Formula + VBA)

### Example 2 – Apply IF Function with OR and AND Statement to Distribute Performance Bonuses

Steps:

• Enter the following formula in cell G5,
`=IF(OR(E5>\$D\$11,AND(E5>\$E\$11,F5=\$F\$11)),1000,0)`
• Pressing the Enter key will display the output 0.

• Drag the AutoFill tool down to fill the rest of the cells in column G.

Formula Breakdown
• OR(E5>\$D\$11 represents Total in column E has to be > 300,000 for the statement to be TRUE.
• AND(E5>\$E\$11, F5=\$F\$11) indicates the statement is TRUE if both Total > 295,000 and Training = Yes are true.
• IF(OR(E5>\$D\$11, AND(E5>\$E\$11, F5=\$F\$11)),1000,0) commands to print 1000 if TRUE otherwise 0.

### Example 3 – Create Gradesheet Calculators Using IF Function with OR and AND Statement

Steps:

• Enter the following formula in cell G5.
`=IF(OR(C5>\$C\$11,D5>\$D\$11,AND(E5=\$E\$11,F5>\$F\$11)),\$G\$12,\$G\$13)`
• Press Enter.

• Use the AutoFill tool to get the rest of the results.

Formula Breakdown
• OR(C5>\$C\$11,D5>\$D\$11 indicates the statement is TRUE either the Test-1 is > 80 or Test-2 is > 75.
• AND(E5=\$E\$11,F5>\$F\$11) represents the statement is TRUE if E5 is = Yes and Attendance% > 70% are true.
• IF(OR(C5>\$C\$11, D5>\$D\$11, AND(E5=\$E\$11, F5>\$F\$11)),\$G\$12,\$G\$13) commands if the conditions are TRUE then display Passed otherwise Failed.
Notes
• While using the formula, referencing an error cell will return a #VALUE!
• The formulaâ€™s evaluation values do not contain commas (,). Doing that will result in a #VALUE!

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF