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

Get FREE Advanced Excel Exercises with Solutions!

By checking for a condition and delivering a result if it is TRUE or FALSE, the IF function enables us to logically compare a value to what we anticipate. What happens, however, if we need to test many conditions, where, for example, all conditions must be True or False (AND), or only one condition must be True or False (OR)?  We can use OR and AND functions independently, but we will frequently see the IF function in combination with them. With that in mind, in this article, we learn 3 advanced examples to use IF function with OR and AND statement in Excel.

## 3 Advanced Examples to Use IF Function with OR and AND Statement in Excel

Before we dive into the examples, let’s first recap the most important part of the formula combination namely the IF function. Comparing thoroughly two or more logical conditions, the IF function returns TRUE or FALSE outputs. Furthermore, we will combine the OR and AND statements with the IF function with hands-on examples to understand better. To demonstrate, let’s take a dataset that represents the financial states of various departments of an organization. Here, we add the proper conditions in row 12 to implement the formula properly. ### Example 1: Utilize IF Function with OR and AND Statement in Excel to Analyze Financial Data

The first example aims to analyze the financial data of the organization by combining the IF function with the OR and AND statements. Here, we will set four logical conditions. The first two conditions are in the OR statement and the other two are in the AND statement. Only one or both conditions can be true for the whole statement to be TRUE in the case of the OR statement. Alternatively, for the AND statement, both statements have to be true for the whole statement to be True. If the conditions are true we will flag the departments by displaying Flag. Otherwise, we will display OK. Let’s see the formula combination below to do the analysis.

Steps:

• Firstly, in cell G5, type the following formula combination.
`=IF(OR(C5>\$C\$12,D5>\$D\$12,AND(E5=\$E\$12,F5>\$F\$12)),"Flag","OK")`
• Further, press the Enter or Tab keys to see the output Flag.
• This represents the conditions are TRUE for the Production department. • Later, use the AutoFill tool to obtain the rest of the outputs.
• Thus, we obtain the results in our dataset with the combined formula. 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

The objective of the second example is to apply 3 conditions using OR and AND statements with the IF function for the distribution of performance bonuses. The conditions are if the Total sales are > 300,000 then give the employee a 1000 bonus. Also, if the Total sales are > 295,000 and Training is = Yes then give him the bonus. Otherwise, do not give any bonus. Follow the instructions for implementation.

Steps:

• To begin with, type 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. • Now, drag the AutoFill tool down to fill the rest of the cells in column G.
• Lastly, we obtain the desired result. 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

In the final example, we will create a grade sheet calculator by applying logical conditions using OR and AND statements combined with the IF function. The conditions are the same as in the first example. We will know more about this below. Therefore, let’s start following the instructions.

Steps:

• Initially, in cell G5, write the following formula.
`=IF(OR(C5>\$C\$11,D5>\$D\$11,AND(E5=\$E\$11,F5>\$F\$11)),\$G\$12,\$G\$13)`
• Afterward, tap the Enter key. • Next, 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! error.
• Moreover, keep in mind that the formula’s evaluation values do not contain commas (,). Doing that will result in a #VALUE! error.

## Conclusion

In conclusion, we have discussed some advanced examples of how to use the IF function with OR and AND statement in Excel with an example. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please, leave any further queries or recommendations in the comment box below.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  