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:
- Open a new Excel worksheet and select a cell where you want to insert the formula.
- 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.”
- 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:
- Logical test: the condition you want to check
- Value_if_true: the value to return if the logical test is true
- 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
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?
Our training programs start at $29 and will help you learn Excel quickly.
this is what I needed. tyty
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?
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))
Thanks a lot, This formula helped me as well.
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.
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”)))
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
I am trying to find the correct formula for:
If A > 40 then = 0
If A < 40 then 40-A = #
Any help welcomed!
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
EVEN I AM STRUGGLING FOR IT
I am trying to write a formula that logically equates to the following and am not able to figure it out!
if column 1 current row = “2024” then if (prior row column k equals current row column k return “no change” otherwise return “change”) otherwise return “0”
I am getting stuck on determining how to have excel compare information on two different rows. Essentially I am trying to figure out if a data point changed year over year from an excel spreadsheet download.
Any help is appreciated. Thanks!!
I am trying to make this formula work, but it’s not working. Can someone help me with this one?
=IFS(AND([@[Length of Service]]>=1;[@[Length of Service]]=40;”40+ years”;[@[Length of Service]]=5;[@[Length of Service]]=10;[@[Length of Service]]=20;[@[Length of Service]]=30;[@[Length of Service]]<40);"30 – 40 years")
Basically I want to auto populate a column (Length by Segment) based on the length of service in company.
The Length by Segment has the following categories: <1 year; 1-5 years; 5-10 years; 10-20 years; 20-30 years; 30-40 years; 40+.
I deeply appreciate if someone can help me sorting out the formula.
Regards.
I could use some assistance if someone can assist me. I want to use this function and if these criteria are met then total up the dollar amounts that would correspond to the counted items in column “O”
=COUNTIFS($D2:D1077,”Task”,$E2:E1077,”>=1/1/23″)-COUNTIFS($D2:D1077,”Task”,$E2:E1077,”>=12/31/23″)
I want to use this function and if these criteria are met then total up the dollar amounts that would correspond to the counted items in column “O”. I cannot get it to work. I dont know if I need to mix in the sum formula with a countifs. If anyone has some input it would be appreciated.
=COUNTIFS(O2:O1077,AND(D2:D1077,”Task”,E2:E1077,”>=1/1/23″))-COUNTIFS(O2:O1077,AND(D2:D1077,”Task”,E2:E1077,”>=12/31/23″))
I need help team2 W/L for column 5
Team1
Score1
Team2
Score2
I am trying to check if a state and email address are the same across two different data sets. Eg [email protected] and Arizona are the matching in both lists. Any help is welcome
I need help team2 W/L for column 5
Team1
Score1
Team2
Score2
Hello. I have a problem. If cells A4:a50 contains a series of various compass points from 0 to 360. For each cell between A4 and A50, I would like to extract only those compass points that fall within a particular range, say, 21 degrees through to <161 degrees. I have tried several variations with the AND, OR, THEN, and ELSE syntax additions, but si far – no luck. Anyone have any ideas??
I am trying to make this formula work, but it’s not working. Can someone help me with this one?
Having 4 working days and 3 off days and want to make a time sheet with mention IF formula above with condition of 10.00 for working days and OFF for non-working days. (OFF days Friday, Saturday & Sunday)
I deeply appreciate if someone can help me sorting out the formula.
How would I do IF with an AND for not equal
If the Date is not blank and if the Status is not “Paid” then calculate
This is what I have but it always comes back as “”(False)
=IF(AND([Date]””,[Status]”Paid”),[Due]-TODAY(),””)
I LIKE TO COMBINE THE INCOME TAX RATE IN A SINGLE FORMULA AS
IF((C3<300000),0)
IF((C3<=600000),(C3-300000)*5%)
IF((C3<=900000),(C3-600000)*5%)+15000
IF((C3<=1200000),(C3-900000)*10%)+15000
AND SO ON !
I have a working formula =IF($C28=BI$16, $M28, “”) to compare cells in columns and rows to return the value in a separate cell if there is a match. Now my data is arriving in two columns and two rows, so I need to compare two cells before returning the value in $M28 and tried this: =IF(AND($C28=BI$16, $D28=BI$20), $I28, “”), but it’s not working. I created this formula based on =IF(AND(A1>50, B1>60), “Pass”, “Fail”), but I want it to return the information in $M28, not Pass or Fail. Any ideas?
I am trying to write a simple conditional formula and am getting stumped. I have 3 date columns: Date Column A (this is a constant), and then if a sooner event happens, a date goes into Column I, and I need to be able to subtract the 2 dates to see how long in between. If the event doesn’t happen sooner then I need the formula to subtract from Column G. So If Column I filled out, subtract Column A from it, if Column G is filled out, subtract Column A from it. Any ideas?
Hi there, I need help with a formula to show the following –
in first cell
12.5% commission when the gross total falls between (£30,001 – £50,000)
In second cell
15% commission when the gross total falls between (£50,001 – £75,000)
I tried the IF(AND) function but the “False Statement” means that the cell will change the max amount once the gross total goes above that threshold. E.g. I need cell one to show the max value of £2499.88 (12.5% 50,000-30.001) once the gross value reaches £50,001.
I apologise this explanation isn’t very clear, I hope it makes sense.
Thanks