Excel IF Function (and multiple conditions)

Do you want to learn how to use Excel’s IF function, including how to consider multiple conditions? If yes, then you have come to the right place! The IF function is one of the most commonly used functions in Excel, and it is essential for data analysis and decision making. In this post, we’ll walk through the basics and then proceed to examples that demonstrate how to analyze multiple conditions or tests.

Let’s get to it.

Video

IF Function Overview

The IF function checks if a condition is true or false and returns a value based on the result. The IF function has three arguments: the logical_test, the value_if_true, and the value_if_false:

=IF(logical_test, value_if_true, value_if_false)

For example, you can use the IF function to check if a student has passed or failed an exam based on their score. If the student’s score is greater than or equal to 60, they have passed the exam, and the function will return “Pass.” If the student’s score is less than 60, they have failed the exam, and the function will return “Fail.” Assuming the score is stored in cell A1, the formula to accomplish this is:

=IF(A1>=60, "Pass", "Fail")
  • The logical_test is: A1>=60
  • The value_if_true is: “Pass”
  • The value_if_false is: “Fail”

The IF function in Excel allows us to perform different calculations or return different results based on specific conditions. In this tutorial, we’ll cover the basics of the IF function and explore a few advanced examples as well.

Basic Example

Let’s say we want to calculate a bonus amount for employees based on their eligibility. If an employee is eligible, their bonus is $500, and if not, their bonus is $0. We want to write a formula in column D and fill that formula down:

We can accomplish this using the following formula in cell D13:

=IF(C13="Y",500,0)

Here, the IF function has three arguments: the test, the value if the test is true, and the value if the test is false. The test checks whether the value in cell A2 is equal to “Y”. If the test is true, the function returns the value 500. If the test is false, the function returns 0.

We fill the formula down, and bam:

Now that we are warmed up, let’s take a look at a few additional examples.

Additional Examples

Exercise 1

Let’s say we want to calculate a bonus based on multiple conditions. If an employee is not eligible, their bonus is $0. If they are eligible, we check whether they are vested or not. If they are vested, their bonus is $500. Otherwise, their bonus is $0.

We can use the following nested IF function to accomplish this:

=IF(C14="N",0,IF(D14="Y",500,0))

Here, the IF function is nested within another IF function. The first test checks whether the value in cell C14 is equal to “N”. If the test is true, the function returns 0. If the test is false, the second test checks whether the value in cell D14 is equal to “Y”. If the test is true, the function returns 500. If the test is false, the function returns 0.

Exercise 2

We can also use the OR and AND functions to check multiple conditions. Let’s say we want to calculate a bonus based on whether an employee is eligible or not and whether they have completed a certain training program or not. If an employee is not eligible or has not completed the training program, their bonus is $0. Otherwise, their bonus is $500. We can use the following IF function with the OR function to accomplish this:

=IF(OR(A2="N",B2="N"),0,500)

Here, the OR function checks whether either the value in cell A2 is equal to “N” or the value in cell B2 is equal to “N”. If either test is true, the OR function returns true and the IF function returns 0. If both OR tests are false, then OR returns false and IF returns 500.

Exercise 3

We can also use the AND function to check multiple conditions. Let’s say we want to calculate a bonus based on whether an employee is eligible and has completed a certain training program. If an employee is eligible and has completed the training program, their bonus is $500. Otherwise, their bonus is $0. We can use the following IF function with the AND function to accomplish this:

=IF(AND(A2="Y",B2="Y"),500,0)

Here, the AND function checks whether both the value in cell A2 is equal to “Y” and the value in cell B2 is equal to “Y”. If both AND tests are true, AND returns true and the IF function returns 500. If either AND test is false, then AND returns false and IF returns 0.

Exercise 4

Okay, here we’re going to use the IFS function. Now, the IFS function is designed to look at a string of conditions, and whichever one hits true first is the one that’s returned. Here’s how we set it up:

=IFS(C14="N",0, D14="N",0, TRUE,500)

Basically, if C14 is equal to “N” then we return zero. If that’s not true, then it’s going to look at the next argument. If D14 is equal to “N” then we return zero. If that’s not true, we look at the next argument. The next argument is TRUE, which always returns true, so the function returns 500. Basically, that final pair is the equivalent of saying “if none of the previous conditions are true, return this.”

So, it’s just going in order. Once one of the logical tests returns true, it stops and ignores the remaining tests. And that’s how you can use the IFS function to handle multiple conditions in Excel.

Frequently Asked Questions

How do you do an IF/THEN formula in Excel?

To create a formula that uses the IF function in Excel, follow these simple steps:

  1. Open a new Excel worksheet and select a cell where you want to insert the formula.
  2. Type =IF( in the cell and then enter the logical test or condition you want to check. For example, =IF(A1>50, “Pass”, “Fail”) will check if the value in cell A1 is greater than 50. If it is, the formula will return “Pass,” and if it is not, the formula will return “Fail.”
  3. Close the parentheses and press Enter. The result of the formula will be displayed in the cell.

How do you put 2 conditions in an Excel IF function?

To put two conditions in an IF formula in Excel, you can use the AND or OR function along with the IF function. For example, =IF(AND(A1>50, B1>60), “Pass”, “Fail”) will check if the value in cell A1 is greater than 50 and the value in cell B1 is greater than 60. If both conditions are true, the formula will return “Pass,” and if either condition is false, the formula will return “Fail.”

How do you put 3 conditions in if Excel?

To put three conditions in an IF formula in Excel, you can use nested IF functions, or use the IFS function instead. To nest multiple IF functions, use the following format =IF(logical_test1, IF(logical_test2, IF(logical_test3,…),…),…). To use the IFS function instead, use the following format =IFS(logical_test1, value_if_true, logical_test2, value_if_true, logical_test3, value_if_true, …).

What is an IF THEN formula?

An IF/THEN formula in Excel is another term for the IF function. The IF function checks if a condition is true or false and returns a value based on the result.

Can I write 2 conditions in an IF statement?

Yes, you can write two conditions in an IF statement using the AND or OR function. You can also use nested IF functions or the IFS function to check multiple conditions.

What are the 3 arguments of the IF function?

The three arguments of the IF function in Excel are:

  1. Logical test: the condition you want to check
  2. Value_if_true: the value to return if the logical test is true
  3. Value_if_false: the value to return if the logical test

Conclusion

I hope that this post provided useful information for how to use the IF function Excel, and, a few alternatives for analyzing multiple conditions. If you have any suggestions, alternatives, or things you’d recommend, please share by posting a comment below … thanks!

Sample file

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

8 Comments

  1. Lisa on October 12, 2023 at 6:45 pm

    this is what I needed. tyty

  2. DJ on October 28, 2023 at 6:03 pm

    What if you need IF with two conditions and AND function together?

    I really need help. I happen to work out a formula, but i need to insert one more condition.

    e.g.
    Column A is Y or N (means YES need follow up or NO means no need)
    Column B is U or MU (means URGENT or MOST URGENT)
    Column C is a DATE

    Formula I need work to show when is date for follow up:
    IF A=Y and B=U is both true or met, results should be C+14days.
    or
    IF S=Y and B=MU is both true or met, results should be C+7days.
    How to combine these two?

    • Ondřej Vašák on December 8, 2023 at 5:37 am

      I think this is what you want:

      IF(AND(A1=’Y’;B1=’U’),C1+14, IF(AND(S1=’Y’,B1=’MU’),C+7,value_if_false))

  3. Kim Busby on November 26, 2023 at 1:58 pm

    I was looking to insert a formula as the true value of an IF formula. Can this be done and if so, how is it done.

  4. Aaliyah on January 4, 2024 at 11:11 am

    I need help and can’t seem to figure out how to look at multiple conditions using different categories.

    Here is my formula which is not returning anything. If I remove thethe string from IF(OR) then it works fine.
    =(IF([@Category]=”shipment”,IF([@[Actual Ship Date]]<[@[Original Promise Date]],"On Time",IF([@[Actual Ship Date]]=[@[Original Promise Date]],"On Time","Late")),IF([@[Original Promise Date]][@[Original Promise Date]],”Customer Changed”,”Miss”)))

  5. Rajesh Thathoo on January 18, 2024 at 1:09 am

    Dear Sir,
    i want to write a formula in excel where we have five Values in horizontal order , if these values are in increasing order The Remark should come as Growth , if these Values are same in all five Cells It should Print Stagnant if These values are in decreasing order it should print De Growth , and if these Five Values are in increasing decreasing order it should Print Fluctuating, THis nested if has to be written in one cell kindly help

  6. AK on January 29, 2024 at 10:30 am

    I am trying to find the correct formula for:

    If A > 40 then = 0
    If A < 40 then 40-A = #

    Any help welcomed!

  7. Top Narayan Shrestha on February 12, 2024 at 2:24 am

    My queries

    Product A with range of weight and determine price base on slab

    Weight range min 560 kg, price $2000
    Weight range >560 -to- 620, price $4400
    Weight range >620-to-999, price $5200

    Can anyone formulate If or IFS

Leave a Comment