How to Combine IF OR Formulas in Excel - Compute Expert

How to Combine IF OR Formulas in Excel


Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> How to Combine IF OR Formulas in Excel





In this tutorial, you will learn how to combine IF OR formulas in excel completely. We sometimes need to combine these two formulas when we need to evaluate multiple logic conditions to get our IF result.

Before you begin learning these formulas’ combination, you better have understood the basics of each formula separately. You can learn IF by clicking here and OR by clicking here if you need to.

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

Learn Excel Dashboard Course






The Way to Write It and Its Inputs

Here is the usual way to write the IF and OR formulas combination in excel.

= IF ( OR ( logic_condition1 , logic_condition2 , … ) , result_if_true , result_if_false )


We input the multiple logic conditions we have to evaluate in our OR and separate them with comma signs ( , ). We write the OR itself in the logic condition input part of our IF.

We input the true and false results of our IF normally. The one that IF will give to us depends on the TRUE or FALSE result that our OR produces.

OR will produce TRUE if at least one of the logic conditions we input into it is TRUE. If all of them are FALSE, then OR will produce FALSE.



Example of Its Usage and Result

Here is the implementation example of the IF and OR combination in excel.

How to Combine IF OR Formulas in Excel - Screenshot of the IF OR Implementation Example

In this example, we want to assess the “pass/fail” status of the test score groups in each row. We can determine the status by checking whether the three test scores in each group pass the passing grade or not. If any one of them passes, then we give the “pass” status.

As any test score that passes is good enough in this situation, we can use IF OR to help us here.

We input all the three scores passing conditions to the OR that we write. As the passing grade for all the scores is 80, we compare each test score with 80 in our OR. The logic conditions we write here are the test scores must be more than 80 (test_score>80).

For the IF true and false results, we input “Pass” and “Fail” marks. If our OR produces TRUE (means at least one of the three test scores in the group passes the passing grade), then our IF will produce “Pass”. If our OR produces FALSE (means none of the three test scores in the group passes the passing grade), then our IF will produce “Fail”.

We do that for all the test score groups and we will get all the assessment results we need. Just as you can see in the example screenshot above!



Writing Steps

After we discussed about the writing form, inputs, and example of IF OR, now let’s discuss about its writing steps. If you are familiar with IF and OR formulas separately, then you should be able to follow these steps without problem!

  1. Type an equal sign ( = ) in the cell where you want to put your IF OR result

    How to Combine IF OR Formulas in Excel - Screenshot of Step 1

  2. Type IF (can be with large and small letters) and an open bracket sign after =

    How to Combine IF OR Formulas in Excel - Screenshot of Step 2

  3. Type OR (can be with large and small letters) and an open bracket sign

    How to Combine IF OR Formulas in Excel - Screenshot of Step 3

  4. Input all the logic conditions you want to process. Separate them with comma signs

    How to Combine IF OR Formulas in Excel - Screenshot of Step 4

  5. Type a close bracket sign if you have inputted all your logic conditions. Then, type a comma sign

    How to Combine IF OR Formulas in Excel - Screenshot of Step 5

  6. Input the IF result you want if at least one of the logic conditions you inputted earlier is true. Then, type a comma sign

    How to Combine IF OR Formulas in Excel - Screenshot of Step 6

  7. Input the IF result you want if all of your logic conditions are false

    How to Combine IF OR Formulas in Excel - Screenshot of Step 7

  8. Type a close bracket sign

    How to Combine IF OR Formulas in Excel - Screenshot of Step 8

  9. Press Enter
  10. Done!

    How to Combine IF OR Formulas in Excel - Screenshot of Step 10






IF OR then Calculate

We can input many things as the true and false results of our IF in the IF and OR formulas combination. One of the things we can input here is a calculation process.

If you need to calculate something depending on your OR TRUE/FALSE result, then just input the calculation in your IF. The IF OR general writing form if we input calculation processes as the IF true and false results is as follows.

= IF ( OR ( logic_condition1 , logic_condition2 , … ) , calculation_if_true , calculation_if_false )


You obviously can input the calculation process only in one of the IF results (true or false). Just input another thing in the IF result where you don’t want to place a calculation if that is the case.

Here is the implementation example of this IF OR concept with calculation processes in excel.

How to Combine IF OR Formulas in Excel - Screenshot of the IF OR with Calculation Process Results Implementation Example

In this example, we give a bonus score to people who get >90 in test 1 or >85 in test 2. To calculate the total scores fast with this particular condition, we use IF OR to help us.

We input the logic conditions of the bonus score in our OR. We also input sum calculation processes as our IF possible results so we can get our total score immediately.

If a person fulfills the bonus score OR condition, then we add 5 to her/his total score sum process. If a person doesn’t, then we just sum to get her/his total score without the bonus score.

We write our IF OR with the calculation processes correctly and we will immediately get the results we want!



IF OR Multiple Conditions

OR can hold up to 255 logic conditions that we input into it. Thus, you should be able to input all the logic conditions you need to evaluate in your IF OR! Just don’t forget to separate those logic conditions you input to OR with comma signs.

Here is the implementation example of the IF OR when we input 5 logic conditions into it.

How to Combine IF OR Formulas in Excel - Screenshot of the IF OR with 5 Logic Conditions Implementation Example

As you can see, our IF OR can work just fine. This will be the case too as long as you stay within the 255 logic conditions limit!



Nested IF OR in Excel

Need to evaluate multiple logic conditions in phases in your IF? If OR can fulfill the evaluation requirements for all your logic conditions, you can nest IF OR to solve that situation!



Here is the general writing form if we try to nest IF OR in excel.

= IF ( OR ( logic_condition1 , logic_condition2 , … ) , result_if_true , IF ( OR ( logic_condition3 , logic_condition4 , … ) , result_if_true , … , result_if_all_false ) )


To nest IF OR, just input the next IF OR as the false result of the previous IF OR. If the OR in the previous IF OR produces FALSE, we will immediately evaluate the OR in the next IF OR.

This kind of process makes us evaluate our multiple logic conditions in phases. Thus, we get what we want previously by using this nested IF OR!

Here is the implementation example of the nested IF OR in excel.

How to Combine IF OR Formulas in Excel - Screenshot of the Nested IF OR Implementation Example

In this example, we want to see whether each two test scores group passes, retakes test, or fails. As we have more than two conditions here and all have multiple logic conditions, we use nested IF OR.

The passing grade has the highest score to pass and thus, we write the IF OR for it first. We input all its logic conditions requirements in its OR and its relevant IF result too.

When its OR produces FALSE, we move on to the second IF OR phase which is for the test retake. We input its logic conditions requirements and relevant IF result too. If this second OR produces FALSE too, then our nested IF OR will produce “Fail”.

We do that for all the two test scores groups. As a result, we get all the assessment results we need as you can see in the example screenshot!



Case Sensitive IF OR

By default, OR isn’t case sensitive when it evaluates the logic conditions we input into it. However, we may sometimes need it to be when we do data comparison in the logic condition we input into it.

So, what to do to solve this kind of problem? Well, you can utilize the excel EXACT formula to help you solving this problem.

EXACT is a formula that compares between two data and produces TRUE or FALSE based on the comparison result. EXACT is case sensitive in its comparison process and thus, it should help us get the right OR result.

Just apply EXACT to our OR data comparison logic condition input where we need it to be case sensitive. Here is the general writing form if we apply EXACT to all logic conditions we input into our OR.

= IF ( OR ( EXACT ( data1 , data2 ) , EXACT ( data3 , data4 ) , … ) , result_if_true , result_if_false )


And here is its implementation example in excel. You can see the results comparison when we use EXACT and when we don’t use EXACT in our IF OR.

How to Combine IF OR Formulas in Excel - Screenshot of the IF OR Implementation Example With EXACT

How to Combine IF OR Formulas in Excel - Screenshot of the IF OR Implementation Example Without EXACT

The examples above should make the EXACT application effect clearer for us! When we use EXACT, excel will be case sensitive when we compare our data. When we don’t use it, however, excel will treat capital and non-capital letters as the same.

Therefore, when you need to be case sensitive in your IF OR, don’t forget to use EXACT!




IF AND OR Statements

AND is another formula in excel which you can use to combine multiple logic conditions and produce one TRUE/FALSE. The difference between AND and OR that we use throughout this tutorial is in their thinking logic.

AND will produce TRUE if all its logic conditions inputs are TRUE. If at least one of them is FALSE, then AND will produce FALSE.

In certain circumstances, we may need to combine OR and AND in our IF to evaluate our multiple logic conditions. If that is the case, then we need to put AND inside our OR or OR inside our AND.

Here is the general writing form for both cases in excel.

IF AND OR with AND inside OR

= IF ( OR ( AND ( logic_condition1 , logic_condition2 , … ) , … ) , result_if_true , result_if_false )


IF AND OR with OR inside AND

= IF ( AND ( OR ( logic_condition1 , logic_condition2 , … ) , … ) , result_if_true , result_if_false )


How you combine OR and AND inside your IF depends on your logic conditions combination needs. Be careful when implementing your OR and AND combination so you can get the correct result from your formula writing!

Here is an implementation example of the IF, AND, and OR formulas combination in excel.

How to Combine IF OR Formulas in Excel - Screenshot of the IF AND OR Implementation Example



Exercise

After you have learned how to combine IF and OR formulas in excel completely, practice your understanding by doing this exercise!

Download the exercise file from the following link and answer the questions. Download the answer key file if you have done the exercise and want to check your answers!

Link to the exercise file:
Download here

Questions

Use the combination of IF and OR to answer all the questions
  1. A product performs well in Region A if it sells >40000 in A1, >35000 in A2, or >45000 in A3. Confirm the sales potency of each product by filling “Yes” or “No” in the column we provide!
  2. A product performs well in Region B if it sells >80000 in B1, >82000 in B2, or >90000 in B3. Confirm the sales potency of each product by filling “Yes” or “No” in the column we provide!
  3. A product performs well in general if it performs in region A or region B. Confirm the overall sales potency of each product by filling “Yes” or “No” in the column we provide!

Link to the answer key file:
Download here



Additional Note

You can nest up to 7 IF OR in excel 2003 and 2007. In excel 2010 onwards, you can nest up to 52 IF OR.

However, you may not want to nest too many IF ORs! After all, that can make your formula writing complicated and hard to edit.



Related tutorials you should learn too:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!





Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!




Learn Excel Dashboard Course

Follow our tutorial content also on


Excel Calculation

Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division in Excel

Average in Excel



Excel Formula

VLOOKUP Function in Excel

IF Function in Excel

SUM Function in Excel

COUNTIFS Function in Excel

SUMIFS Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text in Excel

Excel Worksheet Definition

Excel Range Definition

Excel Shortcuts



Excel Products & Services Recommendation

Best Laptops for Excel

Best Tablets for Excel

Best Keyboards for Excel

Best Mouse for Excel

Best Monitors for Excel



Excel Consultation

Recommended Things

About Us

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2024 Compute Expert