by In this article, you will learn how to build an Excel IF statement for different types of values as well as how to create multiple IF statements.
IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met.
In this tutorial, we are going to learn the syntax and common usages of the Excel IF function, and then take a closer look at formula examples that will hopefully prove helpful to both beginners and experienced users.
IF function in Excel
IF is one of logical functions that evaluates a certain condition and returns one value if the condition is TRUE, and another value if the condition is FALSE.
The syntax of the IF function is as follows:
As you see, IF takes a total of 3 arguments, but only the first one is obligatory, the other two are optional.
Logical_test (required) - the condition to test. Can be evaluated as either TRUE or FALSE.
Value_if_true (optional) - the value to return when the logical test evaluates to TRUE, i.e. the condition is met. If omitted, the value_if_false argument must be defined.
Value_if_false (optional) - the value to return when the logical test evaluates to FALSE, i.e. the condition is not met. If omitted, the value_if_true argument must be set.
Basic IF formula in Excel
To create a simple If then statement in Excel, this is what you need to do:
- For logical_test, write an expression that returns either TRUE or FALSE. For this, you'd normally use one of the logical operators.
- For value_if_true, specify what to return when the logical test evaluates to TRUE.
- For value_if_false, specify what to return when the logical test evaluates to FALSE. Though this argument is optional, we recommend always configuring it to avoid unexpected results. For the detailed explanation, please see Excel IF: things to know.
As an example, let's write a very simple IF formula that checks a value in cell A2 and returns "Good" if the value is greater than 80, "Bad" otherwise:
=IF(B2>80, "Good", "Bad")
This formula goes to C2, and then is copied down through C7:
In case you wish to return a value only when the condition is met (or not met), otherwise - nothing, then use an empty string ("") for the "undefined" argument. For example:
=IF(B2>80, "Good", "")
This formula will return "Good" if the value in A2 is greater than 80, a blank cell otherwise:
Excel If then formula: things to know
Though the last two parameters of the IF function are optional, your formula may produce unexpected results if you don't know the underlying logic.
If value_if_true is omitted
If the 2nd argument of your Excel IF formula is omitted (i.e. there are two consecutive commas after the logical test), you'll get zero (0) when the condition is met, which makes no sense in most cases. Here is an example of such a formula:
=IF(B2>80, , "Bad")
To return a blank cell instead, supply an empty string ("") for the second parameter, like this:
=IF(B2>80, "", "Bad")
The screenshot below demonstrates the difference:
If value_if_false is omitted
Omitting the 3rd parameter of IF will produce the following results when the logical test evaluates to FALSE.
If there is just a closing bracket after value_if_true, the IF function will return the logical value FALSE. Quite unexpected, isn't it? Here is an example of such a formula:
=IF(B2>80, "Good")
Typing a comma after the value_if_true argument will force Excel to return 0, which doesn't make much sense either:
=IF(B2>80, "Good",)
The most reasonable approach is using a zero-length string ("") to get a blank cell when the condition is not met:
=IF(B2>80, "Good", "")
Tip. To return a logical value when the specified condition is met or not met, supply TRUE for value_if_true and FALSE for value_if_false. For the results to be Boolean values that other Excel functions can recognize, don't enclose TRUE and FALSE in double quotes as this will turn them into normal text values.
Using IF function in Excel - formula examples
Now that you are familiar with the IF function's syntax, let's look at some formula examples and learn how to use If then statements in real-life scenarios.
Excel IF function with numbers
To build an IF statement for numbers, use logical operators such as:
- Equal to (=)
- Not equal to (<>)
- Greater than (>)
- Greater than or equal to (>=)
- Less than (<)
- Less than or equal to (<=)
Above, you have already seen an example of such a formula that checks if a number is greater than a given number.
And here's a formula that checks if a cell contains a negative number:
=IF(B2<0, "Invalid", "")
For negative numbers (which are less than 0), the formula returns "Invalid"; for zeros and positive numbers - a blank cell.
Excel IF function with text
Commonly, you write an IF statement for text values using either "equal to" or "not equal to" operator.
For example, the following formula checks the Delivery Status in B2 to determine whether an action is required or not:
=IF(B2="delivered", "No", "Yes")
Translated into plain English, the formula says: return "No" if B2 is equal to "delivered", "Yes" otherwise.
Another way to achieve the same result is to use the "not equal to" operator and swap the value_if_true and value_if_false values:
=IF(C2<>"delivered", "Yes", "No")
Notes:
- When using text values for IF's parameters, remember to always enclose them in double quotes.
- Like most other Excel functions, IF is case-insensitive by default. In the above example, it does not differentiate between "delivered", "Delivered", and "DELIVERED".
Case-sensitive IF statement for text values
To treat uppercase and lowercase letters as different characters, use IF in combination with the case-sensitive EXACT function.
For example, to return "No" only when B2 contains "DELIVERED" (the uppercase), you'd use this formula:
=IF(EXACT(B2,"DELIVERED"), "No", "Yes")
If cell contains partial text
In situation when you want to base the condition on partial match rather than exact match, an immediate solution that comes to mind is using wildcards in the logical test. However, this simple and obvious approach won't work. Many functions accept wildcards, but regrettably IF is not one of them.
A working solution is to use IF in combination with ISNUMBER and SEARCH (case-insensitive) or FIND (case-sensitive).
For example, in case "No" action is required both for "Delivered" and "Out for delivery" items, the following formula will work a treat:
=IF(ISNUMBER(SEARCH("deliv", B2)), "No", "Yes")
For more information, please see:
Excel IF statement with dates
At first sight, it may seem that IF formulas for dates are akin to IF statements for numeric and text values. Regrettably, it is not so. Unlike many other functions, IF does recognize dates in logical tests and interprets them as mere text strings. In other words, you cannot supply a date in the form of "1/1/2020" or ">1/1/2020". To make the IF function recognize a date, you need to wrap it in the DATEVALUE function.
For example, here's how you can check if a given date is greater than another date:
=IF(B2>DATEVALUE("7/18/2022"), "Coming soon", "Completed")
This formula evaluates the dates in column B and returns "Coming soon" if a game is scheduled for 18-Jul-2022 or later, "Completed" for a prior date.
Of course, there is nothing that would prevent you from entering the target date in a predefined cell (say E2) and referring to that cell. Just remember to lock the cell address with the $ sign to make it an absolute reference. For instance:
=IF(B2>$E$2, "Coming soon", "Completed")
To compare a date with the current date, use the TODAY() function. For example:
=IF(B2>TODAY(), "Coming soon", "Completed")
Excel IF statement for blanks and non-blanks
If you are looking to somehow mark your data based on a certain cell(s) being empty or not empty, you can either:
- Use the IF function together with ISBLANK, or
- Use the logical expressions ="" (equal to blank) or <>"" (not equal to blank).
The table below explains the difference between these two approaches with formula examples.
| Logical test | Description | Formula Example | |
| Blank cells | ="" |
Evaluates to TRUE if a cell is visually empty, even if it contains a zero-length string. Otherwise, evaluates to FALSE. |
=IF(A1="", 0, 1)
Returns 0 if A1 is visually blank. Otherwise returns 1. If A1 contains an empty string (""), the formula returns 0. |
| ISBLANK() |
Evaluates to TRUE is a cell contains absolutely nothing - no formula, no spaces, no empty strings. Otherwise, evaluates to FALSE. |
=IF( Returns 0 if A1 is absolutely empty, 1 otherwise. If A1 contains an empty string (""), the formula returns 1. |
|
| Non-blank cells | <>"" | Evaluates to TRUE if a cell contains some data. Otherwise, evaluates to FALSE.
Cells with zero-length strings are considered blank. |
=IF( Returns 1 if A1 is non-blank; 0 otherwise. If A1 contains an empty string, the formula returns 0. |
| ISBLANK() |
Evaluates to TRUE if a cell is not empty. Otherwise, evaluates to FALSE.
Cells with zero-length strings are considered non-blank. |
=IF( Works the same as the above formula, but returns 1 if A1 contains an empty string. |
And now, let's see blank and non-blank IF statements in action. Suppose you have a date in column B only if a game has already been played. To label the completed games, use one of these formulas:
=IF(B2="", "", "Completed")
=IF(ISBLANK(B2), "", "Completed")
=IF($B2<>"", "Completed", "")
=IF(ISBLANK($B2)=FALSE, "Completed", "")
In case the tested cells have no zero-length strings, all the formulas will return exactly the same results:
Check if two cells are the same
To create a formula that checks if two cells match, compare the cells by using the equals sign (=) in the logical test of IF. For example:
=IF(B2=C2, "Same score", "")
To check if the two cells contain same text including the letter case, make your IF formula case-sensitive with the help of the EXACT function.
For instance, to compare the passwords in A2 and B2, and returns "Match" if the two strings are exactly the same, "Do not match" otherwise, the formula is:
=IF(EXACT(A2, B2), "Match", "Don't match")
IF then formula to run another formula
In all of the previous examples, an Excel IF statement returned values. But it can also perform a certain calculation or execute another formula when a specific condition is met or not met. For this, embed another function or arithmetic expression in the value_if_true and/or value_if_false arguments.
For example, if B2 is greater than 80, we'll have it multiplied by 7%, otherwise by 3%:
=IF(B2>80, B2*7%, B2*3%)
Multiple IF statements in Excel
In essence, there are two ways to write multiple IF statements in Excel:
- Nesting several IF functions one into another
- Using the AND or OR function in the logical test
Nested IF statement
Nested IF functions let you place multiple IF statements in the same cell, i.e. test multiple conditions within one formula and return different values depending on the results of those tests.
Assume your goal is to assign different bonuses based on the score:
- Over 90 - 10%
- 90 to 81 - 7%
- 80 to 70 - 5%
- Less than 70 - 3%
To accomplish the task, you write 3 separate IF functions and nest them one into another like this:
=IF(B2>90, 10%, IF(B2>=81, 7%, IF(B2>=70, 5%, 3%)))
For more formula examples, please see:
Excel IF statement with multiple conditions
To evaluate several conditions with the AND or OR logic, embed the corresponding function in the logical test:
For example, to return "Pass" if both scores in B2 and C2 are higher than 80, the formula is:
=IF(AND(B2>80, C2>80), "Pass", "Fail")
To get "Pass" if either score is higher than 80, the formula is:
=IF(OR(B2>80, C2>80), "Pass", "Fail")
For full details, please visit:
If error in Excel
Starting from Excel 2007, we have a special function, named IFERROR, to check formulas for errors. In Excel 2013 and higher, there is also the IFNA function to handle #N/A errors.
And still, there may be some circumstances when using the IF function together with ISERROR or ISNA is a better solution. Basically, IF ISERROR is the formula to use when you want to return something if error and something else if no error. The IFERROR function is unable to do that as it always returns the result of the main formula if it isn't an error.
For example, to compare each score in column B against the top 3 scores in E2:E4, and return "Yes" if a match is found, "No" otherwise, you enter this formula in C2, and then copy it down through C7:
=IF(ISERROR(MATCH(B2, $E$2:$E$4, 0)), "No", "Yes" )
For more information, please see IF ISERROR formula in Excel.
Hopefully, our examples have helped you get a grasp of the Excel IF basics. I thank you for reading and hope to see you on our blog next week!
Practice workbook
Excel IF statement - formula examples (.xlsx file)
4804 comments
Hi,
I am needing a little assistance. I am looking for a formula that will do the following:
If cell A39 is less than zero it will give me the exact amount it shows but if cell A39 is greater than zero it will "-"
Can someone please assist?
Thanks!
Hi Pam,
If my understanding of the task is correct, you can use the following formula:
=IF(A39<0, A39, "-")
Hi
I need a formula that enters the time on a cell if data or text is entered in another cell
so lets say I enter data/text on a1- I would like for the entering of the text to trigger the current time to show on b1
please help!
Hi Leslie,
One of our developers created a macro that will help you, please follow these steps:
- Open your Excel file and press Alt+F11 on your keyboard,
- Double-click the necessary sheet in the list, select "Worksheet" at the top and choose "Change" in the drop-down list in the top-right corner.
- Paste the following code and click Run:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [a1].Address Then
Range("B1").Select
ActiveCell.Value = Time()
End If
End Sub
Hi,
This is probably pretty simple, but I don't work with excel enough to figure it out.
I just want to double check that the numbers that I am inputting in certain cells add up to the total in another cell.
For example:
A2=$7434.54 A4=$5432.78 A6=$2001.76
If this is true I want to leave my test cell (A1) blank.
If this is false I want to populate my test cell (A1) with an x.
The problem I am unable to solve is when A4=$ - , which it sometimes does and is fine. I just keep getting a x, when it should be blank.
Please help if you can. Thanks in advance.
Hi Jason,
If we understand your task correctly and you have a dash in some cells instead of a number like zero, you can use the following formula to calculate the cells without a dash, or show an "X" if there is a dash:
=IF(AND(A6<>" - ",A4<>" - "),A4+A6,"X")
If the task is different, please describe it in more detail.
Hello,
Wondering if you could help me please?
I'm trying to set up a cell so that if all of the cells this one relates to contains a number of 1 or more then it will say yes, if not no.
Its a spreadsheet to help me keep track of things, this cell is whether I have collected quotes from all suppliers. So if I have entered numbers in ALL of the cells, Id like it to say yes, if not no and I have chasing to do.
I've given it a go from what I've read but it wont work. (Although from what I have read I'm thinking it's probably easier to put if all the cells contain something then yes, if blank no)
=IF(AND(Orders!M4=Orders!M4>=1,Orders!T4=Orders!T4>=1,Orders!AA4=Orders!AA4>=1,Orders!AG4=Orders!AG4>=1,Orders!AN4=,Orders!AN4>=1,Orders!AU4=Orders!AU4>=1,Orders!BA4=Orders!BA4>=1,Orders!BG4=Orders!BG4>=1, Orders!BM4= Orders!BM4<=1))"YES","NO"
Many thanks
Hello Vicky,
The last condition in the formula is "less than or equal to 1", it seems like it should look the following way:
=IF(AND(Orders!M4>=1,Orders!T4>=1,Orders!AA4>=1,Orders!AG4>=1,Orders!AN4>=1,Orders!AU4>=1,Orders!BA4>=1,Orders!BG4>=1,Orders!BM4>=1),"YES","NO")
I hope this helps.
if values in cell M2 are between 2 and 5 (2,3,4 and 5)display OK if not NO
Hi Toni,
Here you go:
=IF(AND(M2>=2, M2<=5), "OK", "NO)
Hi Svetlana,
How are you?
I did a pivot table to compare sales growth % between 2015 vs 2014. So for Sales -100% = customer did not buy
Sales -1 to -99 = sales is in deficit
Sales 0 = sales is maintain
Sales above 0 = sales growth
I created the following formula but i am not able to capture the formula for "New" for new customers who did not buy in 2014 (base) but bought in 2015 as the formula came out empty.
=IF(D7>0,"Growth",IF(D7=0,"Maintain",IF(D7>-99,"Deficit",IF(D7=-100,"Did Not Buy","New"))))
Appreciate if you could advise me. Many thanks
Hi there
I am trying to work out how I can use the IF Formula to do the follow:
The idea is that when a date range for organisation staff training is selected in a training information sheet that it will populate the Calendar which is on another sheet with the title of the training.
I am using a date picker/vba form to select the dates in the information sheet.
Each training type has its own sheet with the relevant information on it and the number of these sheets will grow as additional training is added.
Hi Robin,
It is difficult to understand this task without seeing your data. If possible, please send a sample spreadsheet to support@ablebits.com and include a link to this post with your comment number.
We'll do our best to assist you.
If i2=0 f2+h2=value in - sign And
If i2=1 ((f2*g2)+f2)-h2
HI,
I'm doing a vlookup but want to put in a condition, if column E has data, but column F is blank I want it to return a 0 after vlookup, but if column E has 12am-9pm and Column F has 12:54, I want it to return a number 3. This is for login time of person, vs if they are absent. I'm ok with the vlookup but i need a formula that will be 0 if Column F is blank and there is data on E, and if there is data on both column E & F, the return would be 3 or any number I choose. Thanks for your help in advance.
Column E - 12am-9pm
Column F - Blank (no Data)
I am trying to enter a formual.
Max Payable Rate Income Max
EI Max 955.04 1.88 50800
CPP Max 2544.3 4.95 51400
WCB 229.24 0.27 52200
Family Pride
Old Rate Old Gross New Gross New Rate ei
3230.77 $84,000.02 Marilyn $85,680.02 $3,295.39 955.04
1873.75 $48,717.50 Debbie $49,691.85 $1,911.23 FALSE
1821.74 $47,365.24 Stacey $48,312.54 $1,858.18 955.04
1821.74 $47,365.24 Brent $48,312.54 $1,858.18 955.04
1688.38 $43,897.88 Tyler $44,775.84 $1,722.15 955.04
2188.13 $56,891.38 Jodi $58,029.21 $2,231.90 955.04
2583.00 $67,158.00 Sarah $68,501.16 $2,634.66 955.04
Totals $395,395.26 $403,303.17 $15,511.69 $5,730.24
What I am trying to do is a formula for if the new gross pay * the EI Rate / 100 is greater that the max allowable of 955.04, it will enter 955.04, otherwise enter the lower value. I have up to the max but not entering the lower value. Please help.
Thank you.
Hello MaryAnne,
It sounds like you need a formula of this type:
=IF(D11*F11/100>$B$2,955.04,F11)
Here D11 is contains the new gross pay, F11 contains the EI Rate, $B$2 is the cell with the max allowable, and F11 is the cell with the lower value. Please replace the references with the corresponding addresses in your spreadsheet.
Hi,
=IF(A1<10,"",IF(A1<=20,"5",IF(A1<=30,"10",IF(A1<=40,"15",""))))
If 10 to 20= 5
21 to 30 = 10
31 to 40 = 15
Anyone pls sent me the excel formula....
=IF(A1<10,"",IF(A1<=20,"5",IF(A1<=30,"10",IF(A1<=40,"15",""))))
Can the IF function have the "value if true" value as a calculation of variables in the same row??
E.g., Something like this for every row: IF A2=E2, then calculate difference between F2 and C2? if A2 and E2 are not equal, then leave cell blank. Column A and E are numbers, and columns F and C are dates. I want the output to be a calculation (difference in time).
Please let me know! Thanks.
I want to crosscheck many values, for example:
IF x is between 20% and 17% cell value = 30€
IF x is between 16.99% and 14% cell value = 50€
IF x is between 13.99% and 11% cell value = 70€
IF x is between 10.99% and 8% cell value = 80€
IF x is between 7.99% and 5% cell value = 90€
IF x is between 4.99% and 0% cell value = 100€
This is a chart inserted in my excel and the monetary values are a cell each.
The formula I came up with was this:
=SE(F9>0,2001;0;SE(F9=0,2;$B$20;SE(F90,17;$B$20;SE(F9=0,17;$B$20;SE(F90,14;$B$21;SE(F9=0,14;$B$21;SE(F90,11;$B$22;SE(F9=0,11;$B$22;SE(F90,08;$B$23;SE(F9=0,08;$B$23;SE(F90,05;$B$24;SE(F9=0,05;$B$24;SE(F9<0,05;$B$25;0))))))))))))))))))
(assume that the "SE" is "IF", since the excel in my workplace is in Portuguese)
This formula is huge and it takes a while to do, but it works.
My question is if there is anyway to do this faster than what I did here
can any one help me on to explain this formula and i need to include a condition in the same formula that a guest is foreign who will be considered for the counting otherwise it should be Zero
01/01/2016
31/01/2016
Category status
Foreign - 1 Current guest
Local 2 stayed Back
staying back
Examples
Category Departure Arrival time days status
1 01/01/2016 10:52 06/01/2016 14:40 29 4 current guets
=IF(M8="Stayed and Staying back",($H$3-$H$2+1),IF(M8="Stayed Back",(H8-($H$2-1))+(IF(J8=36,1,0))),IF(M8="Staying Back",(($H$3-E8)+(IF(J8=36,1,0)))),(IF(M8="Current Month Guest",((H8-E8)+(IF(J8=36,1,0)))))))))
please help me as soon as possible
Hi - looking to find out if we can use "IF" with date column or if there is another formula / solution to solve this.
I want dates in a column to appear as: dd-mmm-yyyy
However, in some cases we do not have a dd, we only have the mmm-yyyy. In these cases I would like to ONLY display mmm-yyyy. (Currently excel autofills with the first of the month if no dd is entered.) We would like to stop excel from autofilling the missing dd with the 01.
Thanks in advance for any help the community can offer.
Heya, looking for a solution to this please,
If 'wall value =1000 or less then use 600 door, if its between 1000&2000 use door 700 and so on'
hope that makes sense. thanks
hi,
=IF(A11000,A1<=2000),"700",""))
Hi Paul,
Try this one:
=IF(A1<=1000, 600, IF(A1<=2000, 700, ""))
If you want to return text strings rather than numbers, enclose the values in double quotes:
=IF(A1<=1000, "600 door", IF(A1<=2000, "700 door", ""))
Cool, thanks a lot x
Thanks David,
Heya, looking for a solution to this please,
If 'wall value =1000 or less then use 600 door, if its between 1000 - 2000 use door 700 and so on'
hope that makes sense. thanks
if the cell is blank the value come yes other wise no. can u help me for this
=IF(A1="","no","yes")
Hi,
I have the following problem that I want to solve in excel:
I have two rows with values- first column with Quality class (which is from 1 to 4) and the second column which is with diameters (from 2 to 50 almost from one to one). All these values are introduced aleatory are are almost thousands of combinations. Separately I want to order them and count them on quality class and diameters. Something like quality I - diameter 2 how many they are ?. So separately I want to have on rows the quality classes (1,2,3,4) and on column the diameters and for each combination quality class - diameter to have the count. How can I build an if function to go through the values of the two columns and have their sum.
I hope I made myself understood.
All the best
Isabela
Hi,
Sorry for bothering you. I solve it by using COUNTIFS.
All the best!
Isabela