The IF function is one of the most popular and useful functions in Excel. You use an IF statement to ask Excel 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 Excel IF function, and then will have a closer look at formula examples that will hopefully prove helpful both to beginners and experienced Excel users.
The IF function is one of Excel's logical functions that evaluates a certain condition and returns the value you specify if the condition is TRUE, and another value if the condition is FALSE.
The syntax for Excel IF is as follows:
As you see, the IF function has 3 arguments, but only the first one is obligatory, the other two are optional.
For example, your logical test can be expressed as or B1="sold", B1<12/1/2014, B1=10 or B1>10.
For example, the following formula will return the text "Good" if a value in cell B1 is greater than 10: =IF(B1>10, "Good")
For example, if you add "Bad" as the third parameter to the above formula, it will return the text "Good" if a value in cell B1 is greater than 10, otherwise, it will return "Bad": =IF(B1>10, "Good", "Bad")
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 beneath the hood.
If the value_if_true argument is omitted in your Excel IF formula (i.e. there is only a comma following logical_test), the IF function returns zero (0) when the condition is met. Here is an example of such a formula: =IF(B1>10,, "Bad")
In case you don't want your Excel IF statement to display any value when the condition is met, enter double quotes ("") in the second parameter, like this: =IF(B1>10, "", "Bad")
. Technically, in this case the formula returns an empty string, which is invisible to the user but perceivable to other Excel functions.
The following screenshot demonstrates the above approaches in action, and the second one seems to be more sensible:
If you don't care what happens when the specified condition is not met, you can omit the 3rd parameter in your Excel IF formulas, which will result in the following.
If the logical test evaluates to FALSE and the value_if_false
parameter is omitted (there is just a closing bracket after the value_if_true
argument), the IF function returns the logical value FALSE. It's a bit unexpected, isn't it? Here is an example of such a formula: =IF(B1>10, "Good")
Putting a comma after the value_if_true argument forces your IF statement to return 0, which doesn't make much sense either: =IF(B1>10, "Good",)
And again, the most reasonable approach is to put "" in the third argument, in this case you will have empty cells when the condition is not met: =IF(B1>10, "Good", "")
For your Excel IF formula to display the logical values TRUE and FALSE when the specified condition is met and not met, respectively, type TRUE in the value_if_true
argument. The value_if_false
parameter can be FALSE or omitted. Here's a formula example:
=IF(B1>10, TRUE, FALSE)
or
=IF(B1>10, TRUE)
If you want "TRUE" and "FALSE" to be usual text values, enclose them in "double quotes". In this case, the returned values will be aligned left and formatted as General. No Excel formula will recognize such "TRUE" and "FALSE" text as logical values.
Instead of returning certain values, you can get your IF formula to test the specified condition, perform a corresponding math operation and return a value based on the result. You do this by using arithmetic operators or other Excel functions in the value_if_true
and /or value_if_false
arguments. Here are just a couple of formula examples:
Example 1: =IF(A1>B1, C3*10, C3*5)
The formula compares the values in cells A1 and B1, and if A1 is greater than B1, it multiplies the value in cell C3 by 10, by 5 otherwise.
Example 2: =IF(A1<>B1, SUM(A1:D1), "")
The formula compares the values in cells A1 and B1, and if A1 is not equal to B1, the formula returns the sum of values in cells A1:D1, an empty string otherwise.
Now that you are familiar with the Excel IF function's syntax, let's look at some formula examples and learn how to use IF as a worksheet function in Excel.
The use of the IF function with numeric values is based on using different comparison operators to express your conditions. You will find the full list of logical operators illustrated with formula examples in the table below.
Condition  Operator  Formula Example  Description 
Greater than  >  =IF(A2>5, "OK",) 
If the number in cell A2 is greater than 5, the formula returns "OK"; otherwise 0 is returned. 
Less than  <  =IF(A2<5, "OK", "") 
If the number in cell A2 is less than 5, the formula returns "OK"; an empty string otherwise. 
Equal to  =  =IF(A2=5, "OK", "Wrong number") 
If the number in cell A2 is equal to 5, the formula returns "OK"; otherwise the function displays "Wrong number". 
Not equal to  <>  =IF(A2<>5, "Wrong number", "OK") 
If the number in cell A2 is not equal to 5, the formula returns "Wrong number "; otherwise  "OK". 
Greater than or equal to  >=  =IF(A2>=5, "OK", "Poor") 
If the number in cell A2 is greater than or equal to 5, the formula returns "OK"; otherwise  "Poor". 
Less than or equal to  <=  =IF(A2<=5, "OK", "") 
If the number in cell A2 is less than or equal to 5, the formula returns "OK"; an empty string otherwise. 
The screenshot below demonstrates the IF formula with the "Greater than or equal to" logical operator in action:
Generally, you write an Excel if statement with text using either "equal to" or "not equal to" operator, as demonstrated in a couple of IF examples that follow.
Like the overwhelming majority of Excel functions, IF is caseinsensitive by default. What it means for you is that logical tests for text values do not recognize case in usual IF formulas.
For example, the following IF formula returns either "Yes" or "No" based on the "Delivery Status" (column C):
=IF(C2="delivered", "No", "Yes")
Translated into plain English, the formula tells Excel to return "No" if a cell in column C contains the word "Delivered", otherwise return "Yes". At that, it does not really matter how you type the word "Delivered" in the logical_test argument  "delivered", "Delivered", or "DELIVERED". Nor does it matter whether the word "Delivered" is in lowercase or uppercase in the source table, as illustrated in the screenshot below.
Another way to achieve exactly the same result is to use the "not equal to" operator and swap the value_if_true and value_if_false arguments:
=IF(C2<>"delivered", "Yes", "No")
If you want a casesensitive logical test, use the IF function in combination with EXACT that compares two text strings and returns TRUE if the strings are exactly the same, otherwise it returns FALSE. The EXACT functions is casesensitive, though it ignores formatting differences.
You use IF with EXACT in this way:
=IF(EXACT(C2,"DELIVERED"), "No", "Yes")
Where C is the column to which your logical test applies and "DELIVERED" is the casesensitive text value that needs to be matched exactly.
Naturally, you can also use a cell reference rather than a text value in the 2^{nd} argument of the EXACT function, if you want to.
If you want to base your condition on a partial match rather than exact match, an immediate solution that comes to mind is using wildcard characters (* or ?) in the logical_test argument. However, this simple and obvious approach won't work. Many Excel functions accept wildcards, but regrettably IF is not one of them.
A solution is to use IF in combination with ISNUMBER and SEARCH (caseinsensitive) or FIND (casesensitive) functions.
For example, if No action is required both for "Delivered" and "Out for delivery" items, the following formula will work a treat:
=IF(ISNUMBER(SEARCH("deliv",C2)), "No", "Yes")
We've used the SEARCH function in the above formula since a caseinsensitive match suits better for our data. If you want a casesensitive match, simply replace SEARCH with FIND in this way:
At first sight, it may seem that IF formulas for dates are identical to IF statements for numeric and text values that we've just discussed. Regrettably, it is not so.
Unlike many other Excel functions, IF cannot recognize dates and interprets them as mere text strings, which is why you cannot express your logical test simply as >"11/19/2014" or >11/19/2014. Neither of the above arguments is correct, alas.
To make the Excel IF function recognize a date in your logical test as a date, you have to wrap it in the DATEVALUE function, like this DATEVALUE("11/19/2014"). The complete IF formula may take the following shape:
=IF(C2<DATEVALUE("11/19/2014"), "Completed", "Coming soon")
As illustrated in the screenshot below, this IF formula evaluates the dates in column C and returns "Completed" if a game was played before Nov11. Otherwise, the formula returns "Coming soon".
In case you base your condition on the current date, you can use the TODAY() function in the logical_test argument of your IF formula. For example:
=IF(C2<DATEVALUE("11/19/2014"), "Completed", "Coming soon")
Naturally, the Excel IF function can understand more complex logical tests, as demonstrated in the next example.
Suppose, you want to mark only the dates that occur in more than 30 days from now. In this case, you can express the logical_test argument as A2TODAY()>30. The complete IF formula may be as follows:
=IF(A2TODAY()>30, "Future date", "")
To point out past dates that occurred more than 30 days ago, you can use the following IF formula:
=IF(TODAY()A2>30, "Past date", "")
If you want to have both indications in one column, you will need to use a nested IF function like this:
=IF(A2TODAY()>30, "Future date", IF(TODAY()A2>30, "Past date", ""))
If you want to somehow mark your data based on a certain cell(s) being empty or not empty, you can either:
The table below explains the difference between these two approaches and provides formula example.
Logical test  Description  Formula Example  
Blank cells  =""  Evaluates to TRUE if a specified cell is visually empty, including cells with zero length strings.
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 specified cell contains absolutely nothing  no formula, no empty string returned by some other formula.
Otherwise, evaluates to FALSE. 
=IF(ISBLANK(A1), 0, 1)
Returns the results identical to the above formula but treats cells with zero length strings as nonblank cells. That is, if A1 contains an empty string, the formula returns 1. 

Nonblank cells  <>""  Evaluates to TRUE if a specified cell contains some data. Otherwise, evaluates to FALSE.
Cells with zero length strings are considered blank. 
=IF(A1<>"", 1, 0)
Returns 1 if A1 is nonblank; otherwise returns 0. If A1 contains an empty string, the formula returns 0. 
ISBLANK()=FALSE  Evaluates to TRUE if a specified cell is not empty. Otherwise, evaluates to FALSE.
Cells with zero length strings are considered nonblank. 
=IF(ISBLANK(A1)=FALSE, 0, 1)
Works the same as the above formula, but returns 1 if A1 contains an empty string. 
The following example demonstrates blank / nonblank logical test in action.
Suppose, you have a date in column C only if a corresponding game (column B) was played. Then, you can use either of the following IF formulas to mark completed games:
=IF($C2<>"", "Completed", "")
=IF(ISBLANK($C2)=FALSE, "Completed", "")
Since there are no zerolength strings in our table, both formulas will return identical results:
Hopefully, the above examples have helped you understand the general logic of the IF function. In practice, however, you would often want a single IF formula to check multiple conditions, and our next article will show you how to tackle this task. In addition, we will also explore nested IF functions, array IF formulas, IFEFFOR and IFNA functions and more. Please stay tuned and thank you for reading!
3,786 Responses to "Using IF function in Excel: formulas for numbers, text, dates, blank cells"
Assign sequential numeric numbers to each company with IF funciton. If a company name is repeated, it will have the same number.
anyone please slve this?
Hi, I just want the formula like I just evolving each cell's values like marks. If more than (for example >=50) just want to show that value only using.
For example, someone takes 40, 45, 50, 30 evolving all values and retrieve only more than 50 values.
Make sure the brackets are full closed .This will prevent the repetion.
Very Nice, i got it what i wanted thank you.
Hi,
I have D9 = 0 to 2000, I would like to write this formula in C10
if the value of D9 is equal or greater than 15, then cell C10 value will be 15, otherwise, the value of cell C10 should equal to the value in D9
=IF(D9>=15,C10=15,C10=D9)
But, it doesn't give what I want. anyone can help?
Thank you
remove the C10=
hi! like to corelate data in 2 sheets of same excel file. e.g. sheet 1 and sheet 2
I need help to create if or
any formula like
=if(sheet1b2=10,"sheet2A3=Sheet1A3","blank")
plz help
write semicolon instead comma
=IF(Sheet1!B2=10,Sheet1!A3,"")
Put this formula cell A3 of Sheet2.
Can u direct me on the error of my formula? =IF(E22>0)B22+E22
I want it to be cell B22+E22. But ONLY if there is an entry in E22. If E22 hasn't been filled in yet, then I want the formula to be in place but not to SHOW. Much Appreciated!
=IF(E22="","",IF(E22>0,B22+E22))
Can you help me write a formula for the following situation plse.
If A1A2 is less than zero, then show the negative value. If not, then show zero.
Tks
Hi Jaime,
Here you go:
=IF(A1A2<0, A1A2, 0)
Hello Jaime,
The following formula should do the trick for you:
=IF(A1A2<0, A1A2, 0)
Hi Sveetlana Cheusheva
Is it possible that if I enter any text in column"P", "L" should show what is there in "K".
=IF(NOT(ISBLANK(P7)),"0","=K7")
=IF(ISBLANK(P7)),"=K7","0")
Hi Ashwini,
I think you've overthought it :) A simpler formula will work:
=IF(P7<>"", K7, 0)
i using if condition formula it taking only 10 conditions is it only 10 conditions allowed
Can you help me to accurately represent this formula please:
=IF(L4>0612,"High Risk")
Hello Ricardo,
Please try to add quotes so that the formula will look like:
=IF(L4>"0612", "High Risk", "")
IF Early Bird criteria  Customers who want to avail of an Early Bird discount must place their orders on weekdays between 11:45 a.m.  12:00 p.m. (for lunch) or 6:45 p.m.  7:30 p.m. (for dinner).
Write a logical function to find out whether a customer is an early bird. (refer to the 'Early Bird' criteria stated above) In your function, you will have to reference Column E values (day of week) and Column F (time of order) to determine whether customers are early birds. Think about how you will reference the early bird criteria.
If a customer is an early bird, the function should return "Early Bird". Otherwise, the function should return a blank value.
Hi Svetlana,
I am trying to achieve in the formula to show the following:
If the cell drop down is selected with "CB2" is standalone to show "local", if "CB2" & "CB3" (combined) to show "local & far" and if "CB3" to show "far"
How can I achieve this?
TIA
Hi Tia,
There seems to be a problem with the IF(AND()) function. The comment truncates the formula for some reason.
=IF(A1="CB2","LOCAL",IF(AND(A1="CB2",A1="CB3"),"LOCAL & FAR",IF(A1="CB3","FAR","")))
Good Morning,
I am trying an exercise in Excel, we have a sheet some details, however, in the first column A2, have some digit number in the same column digit will be sometime 7, 8, 10 and some case digit numbers with dots then TWO digits.
I am looking for a formula that can help me to write text in the next column............
Example : =IF(A2=(len)8,"NOBILIA",IF(A2=(len)7,"IMPULS",IF(A2=(len)10,"LINEA DÉCOR"))).
Will you please help me with it.
Sincerely yours
Ikram Siddiqui
=IF(LEN(A4)=7,"IMPULS",IF(LEN(A4)=8,"NOBILIA",IF(LEN(A4)=10,"LINEA DECOR","")))
I would like to check a cell for currency format and if its in £ then multiply it by the $ factor to convert it to $s' and if its in $ then leave it in $s'.
example: A1 is the £ to $ currency factor; A2 is the value in £ or $; A3 will be the value of A1 in $s.
I have tried using the IF function, but it will not recognise A1 currency format! I would appreciate some help with this. Many thanks.
Assuming that A1 is 2006 and B1 is 2006
=IF(A1=B1,"True","False") comes up True
but
=IF(A1="2006","True","False") comes up False.
How do I resolve this within a formula since the 2006 is coming from a different formula.
Thanks in advance.
Please show me formula for the below. Thank you!
IF A1 = ABC B1 = 1234; if A1 change to DEF then B1=5678; also if A1 change to GHI then B1=91110
This will only work on 4 letters at a time. I'm assuming you meant ABC = 123 and not 1234. If not this wont work for you.
=IF(MID(A1,1,1)="", "", CODE(UPPER(MID(A1,1,1)))64&""&IF(MID(A1,2,1)="", "", CODE(UPPER(MID(A1,2,1))) 64&""& IF(MID(A1,3,1)="", "", CODE(UPPER(MID(A1,3,1)))64&""& IF(MID(A1,4,1)="", "", CODE(UPPER(MID(A1,4,1)))64))))
Actually I need 6 digits (ABC=123456). So it doesn't work for me! Thanks for trying!
Here is the 6 letter version. To add more numbers you just have to copy everything from IF to the second & and change the number after the A1.
=IF(MID(A1,1,1)="", "", CODE(UPPER(MID(A1,1,1)))64&""&IF(MID(A1,2,1)="", "", CODE(UPPER(MID(A1,2,1))) 64&""& IF(MID(A1,3,1)="", "", CODE(UPPER(MID(A1,3,1)))64&""& IF(MID(A1,4,1)="", "", CODE(UPPER(MID(A1,4,1)))64&""&IF(MID(A1,5,1)="", "", CODE(UPPER(MID(A1,5,1)))64&""&IF(MID(A1,6,1)="", "", CODE(UPPER(MID(A1,6,1)))64))))
Hello Cindy,
Please try to use the formula below to solve your task:
=IF(A1="ABC", 1234, IF(A1="DEF", 5678, IF(A1="GHI", 91110, 0)))
I Tried several times, but it doesn't work. Could you please check and advise again. Thank you!
Hi Cindy,
Try using commas instead of semicolons to separate the arguments (that depends on which character is set as the List Separator in your Regional Settings):
=IF(A1="ABC", 123456, IF(A1="DEF", 5678, IF(A1="GHI", 91110, "")))
Thank you for replying, Cindy.
Most likely it is the delimiter that was causing troubles. Please try this formula instead, it should work:
=IF(A1=”ABC”, 1234, IF(A1=”DEF”, 5678, IF(A1=”GHI”, 91110, 0)))
I've updated the formula in my previous comment as well. Sorry for the confusion.
It works now. Thank you so much!
Glad to hear that, Cindy!
Plz help.....
If i write a number greater then 100 in a cell mistakenly. I want Excel shows an error in that cell.
For example
I write 23 26 56 100 in cells and write mistakenly 105. So i want to show 105 in error.
I would just highlight the whole column and do a Conditional Formatting, Highlight Cell Rules, Greater Than..., 100. Then if the cell is highlighted in red you'll know it's over 100.
Hey,
Your tutorial is great. I am trying to get this nested formula but somehow its only picking up last logical test values. Can you please check & advise.
=IF(B$4>B7<B$3, "Expiring within 2 Weeks", IF(B$3<B7B$2, "Expired", "Valid")))
Regards
What is the cell you're trying to find the value of? B7?
I would trying using IF(AND and doing it that way
Instead of =IF(B4>B7B3,B7<B4),"True","False")
oops, for some reason it got cut off.
Instead of =IF(B4>B7B3,B7<B4),"True","False")
Not sure why it's cutting off so I'll try one more time. "=IF(AND(B7>B3,B7<B4),"True","False")"
Hello Syed,
Here is the updated formula for you:
=IF(AND(B$4 > B7, B7 < B$3), "Expiring within 2 Weeks", IF(AND(B$3 < B7, B7 > B$2), "Expired", "Valid"))
Depending on the city, you will either take a shuttle to/from the airport or rent a car. Insert an IF function that compares to see if Yes or No is located in the Rental Car? Column for a city. If the city contains No, display the value in cell F2. If the city contains Yes, display the value in the Rental Car Total (F4)
Hello,
The formula below should do the job for you:
=IF(E1="Yes", F2, IF(E1="No", F4, ""))
Hi is it possible to do an if formula when a cell is either finance or cash have it do another formula to equal commission?
I need to lock one column so the formula is displayed but do not want the rest of the spreadsheet formulas to show. How can you freeze one column display the formula?
You can remove the "=" so it does not act as a formula.
Hi, I'm trying to write an if/then statement that will represent numerical values. For example, If L48 is greater than L47, then show the value inside L48. If not, show the value inside L47.
I get it to do the yes, no feature, but I'm having trouble getting it to display the value inside of L48.
=IF(L48>L47,L48,L47)
Hi,
I have the conditional formula on my J cell that read as follows: =IF(G3="F2F","1", "0"). G3 has a drop down menu to choose from and one of the options is F2F. How do I add all the ones at the end of my J cells?
Thank you,
Elida
Do you want different values for the different drop down values in G3? Otherwise, use the formula below. This will give you a 1 if G3 has either F2F,F2F2, or F2F3 (replace with whichever is on your list)
=IF(OR(G3="F2F",G3="F2F2",G3="F2F3"),1,0)
i have the following formula:
=if(isblank(A1),"", if(D1<0, weeknum(A1,1),""))
working with this formula:
=if(isblank(J2),"", hlookup(max(#REF!),$R$1:$DD$89,row(J2),false))
to calculate weeks of perfect attendance. However, it has errored out for anything after week 52. how would i fix this to get it to continue past week 52.
I want to use the IF function to test if one cell (say H5) is above zero, then insert a given cell's value (say the numerical value in C5), if the test is false, then insert another cell value (say the numerical value in E5).
=If(H5>0,C5,E5)
Hi,
I need an help to auto calculate the order quantity, if a particular cell is less than eg : 60.
I want to make a one formula in Excel for:
IF 825>the answerthe answer4001,*0.5
if C1, C3 & C5 have "text", how to total them in C10?
(There are approx. 20 cells with or without text that would report to C10.)
Example: C1 = smith. C3 = . C5 = jones. In this case, I want C10 to show the number "2", allowing me to calculate off of that.
Thanks in advance!
Hi James,
To count cells with text, you can use the COUNTIF function with "*" as the criteria. For example:
=COUNTIF(C1:C9, "*")
Please help me build a formula as follows:
if A1 contains A,B or D then it is CLOSED, if A1 contain C then it is OPEN
thanks
=IF(OR(A1="a",A1="b",A1="d"),"closed",IF(A1="c","open",""))
Should work for your formula.
Hi,
I am trying to have the value of a cell(B2) in Sheet2 populate in Sheet1 cell(B3). I thought I could do ='Sheet2'!B2, but when there is no data in Sheet2 B2 the value populates as 0 in B3, I would like it to remain blank.
=IF(Sheet2!B2=0,"",Sheet2!B2)
That should work.
Thank you! Works like a charm!
I'm trying to get an if, then formula to generate a number in a cell if the adjacent cell is a certain letter (for GPA calculations), but i keep getting a value error message. Here is what I wrote: =IF(C3="A",4,IF(C3="b",3,IF(C3"c",2,IF(C3="D",1,IF(C3="F",0)))))
That formula works except for when the value is "C".
Change the "" into "=".
I have a spreadsheet, with multiple tabs I need to be able to Concatenate a text cell (A3) with a date cell(B3) on a report form, but be blank when the date cell (B3) is empty. this is what I have so Far.
=IF(AND(NOT(ISBLANK('Super(No7)'!A3))*NOT(ISBLANK('Super(No7)'!B3))),CONCATENATE('Super(No7)'!A3&""&TEXT('Super(No7)'!B3,"DD/MM/YYYY"),"")).
The above formula works until I add the &TEXT& bit then it returns FALSE. can anyone help please.
Please i need you assistance to building formula as follows:
if A1 contains specific text, A OR B OR C = CLOSED
if A1 contains specific text, C = OPEN
Many Thanks.
Id like to ask how to use if function or whatever easy function can be used should I want to display the percentage in a cell if a condition has been met. The condition is if all specified range of cells contains any values like a word or date. Lets just say that cell A1 will display the percentage of completion if cells ranging from A2,A3,A4,A5,A6 contains a value like yes or no or a date.
Hi Jason,
It's not clear how you calculate the percentage of completion, but the idea is to count nonempty cells in the range A2:A6, and if the count is equal to 6, display the percentage, otherwise return an empty string:
=IF(COUNTIF(A2:A6, "<>"&"")=5, percentage formula, "")
Hi
I have forgotten how to repeat a certain set of numbers for a spreadsheet.
For example i have a list of 3 items and i want the IF function to repeat as 123123 etc.
I have put 3 in the A1 cell and the number 1 in A2.
Thanks
what is the formula for interval,
fill the cell outstanding if score 4.5 to 5
fill the cell V.Good if the score between 3.5 to 4.4, and so on..
i tried to make rounding but it doesn't work if the number not whole even after rounding
This one should work. You can change the text results to whichever you think fits better.
=IF(E5>=4.5,"OUTSTANDING",IF(AND(E5>=3.5,E5=2.5,E5=1.5,E5<2.5),"SATISFACTORY",IF(E5<1.5,"FAIL","")))))
HI, May u help me with this situation?i cant get the right formula for that 0 or negative figure is equal to 0
0 or negative =0
>0 10% = 1
>10% = 2
i have this as my current =IF(G5>10,"2",IF(G5<=10,"1",IF(G5<0,)))
=IF(G7>10,2,IF(AND(G70),1,IF(G7=0,0,"")))
Hope that helps.
=IF(G5>10,2,IF(AND(G50),1,IF(G5=0,0,"")))
Sorry, the original formula is broken, not sure why it did not paste properly.
Sorry, guys, our blog engine sometimes breaks formulas in ">" and "<" symbols.
If my understanding of the task is correct, this formula should work a treat:
=IF(G5>10, 2, IF(G5>0, 1, 0))
=IF(G5>10,2,IF(AND ( G5 0),1,IF(G5=0,0,"")))
The website seems to break the formula... I tried adding spaces to see if it will work...
It seems the IF(AND( function is breaking the formula.
=IF(G5>10,2,
IF(AND[G50],1,
IF(G5=0,0,"")))
Hello, how do I make an if statement see the text in a cell and not a reference? In Cell F2 the value is "T19" I want to say, if F2 = T19 than "A19" with A19 being text not a reference.
Hi Lee,
=IF(F2="T19","A19","")
Is that what you meant?
Hi Svetlana,
I would like to return a value of "No" in B1 if A1 is either "0" or "na".
Is this possible?
Thanks!
Hi Brett,
Sure. Please use this formula for B1:
=IF(A1="", "", IF(OR(A1=0, A1="na"), "No", ""))
Hi!
I would to ask for your help with this formula.
=IF((T17+U17)V84V84,Z17,0))
I got the same result which is 00:00 even if it has higher or lower value.
Thanks!
=IF((T17+U17)V84V84,Z17,0))
Need help.. is this possible?
If value of A1 is item no. And value of A2 is the qty (i.e. 3 pcs), can A1 be copied in column C thrice? A1 = C1 = C2 = C3.
Hello,
Need one help. I have two values in cell A (Y or N) and i want to use IF statement but i am getting error. The statement is like this if the cell A contains N then it should return N else it should leave blank
IF(A=N,"N","")
Hi Madhosh,
Be sure to always enclose text values in quotations marks and use appropriate cell references. For example:
=IF(A2="N","N", "")
This formula goes to any empty cell in row 2, and then you can drag it down to as many cells as needed.
Hi,
For exm, We are receiving any project 12345 between 6:30:00 AM to 10:30:00 PM then time should automatically 6:30:00 AM.
Could you please help, I want to write if E5 is greater or smaller than 0 enter that amount in C6
Hi Chris,
The formula below should work.
=IF(OR(E5>0,E5<0,),E5,"")
Can anyone help
=IF(A4="Month"&B4="US:W2 Salaried",173.33)
why it is returning false statement
Hi Ankur,
=IF(AND(A4="Month",B4="US:W2 Salaried"),173.33,"")
Was that what you were trying to do?
Hello, trying to get the word "OFF" to register as a zero value in a formula.=IF(D6=D6,IF(B6="OFF",0)C6) The problem I have is this results in a negative number. I need B6 to equal 0 ONLY when the word OFF is entered there otherwise use the number entered. Here is the next cell down on the table I am creating hope this explains the D6=D6,
=IF(D7=D6,IF(B7"OFF",0)C7) . Thanks
Hello, trying to get the word "OFF" to register as a zero value in a formula.=IF(D6=D6,IF(B6="OFF",0)C6) The problem I have is this results in a negative number. I need B6 to equal 0 ONLY when the word OFF is entered there otherwise use the number entered. Here is the next cell down on the table I am creating hope this explains the D6=D6,
=IF(D7=D6,IF(B7="OFF",0)C7) . Thanks
SOLVED  =IF(D6=D6,IF(B6="OFF",0,B6)C6)  Thanks anyway.
Excel 2017 Have 4 columns, need an if statement to add dollar amount in the 4th column. Three columns are designated as A, B, & C, 4th is Amount. If I put an X in Column A , needs to show $10 in column 4, if i put an x in column B, needs to show $5 in column 4, if I put an X in column C, needs to show $1 in column 4.
The first one I can get to work, not the others. In column 4 I put this in:
=IF(A1="X",10,"")
Hi Harold,
=IF(A1="X",10,IF(B1="X",5,IF(C1="X",1,""))
Is that what you're looking for?
Need some help please... I know I am probable over thinking this...
so I am running three columns of numbers say , E,F,G.
Column E is a Negative and Subrtacts From G
Column F is a Positive and Adds to Column G
But I would like a running total in C4, I was thinking that I could use the "IF" to read the last filled in cell in column G. so for example
Cell C4 will show the running total amount without having to scroll down to G47...
data starts in G6 and ends in G90 and with each entry it moves down to the next cell which is blank.
I tried using the "lookup" to show me the value of the cell above the first blank cell but cant figure it out either...
Geez I hope I explained this right.... compare it to a checkbook....
Column E are withdrawls and deduct from G
Column F are Deposits and Add to the current total in G
I have all of the formulas for G and keeping the total but would like a "quick" glance to see the running total....
Hi Steven,
=SUM(G6:G100)+SUM(F6:F90)SUM(E6:E90)
I'm not sure if that is what you mean.
Hi, Could you please help me? I need a function. B6 to B8 each have a drop down list and in C6 I need either "Section booked" if any of B6, B7 or B8 has any text. If all blank then "Select"
It has been driving me crazy. Thank you so much
Hi Barbora,
Please see below.
=IF(OR(B6"",B7"",B8""),"SECTION BOOKED","SELECT")
Hi Barbora,
Replace "greaterthanlessthan" with the operators
=IF(OR(B6 greaterthanlessthan "",B7 greaterthanlessthan "",B8 greaterthanlessthan ""),"SECTION BOOKED","SELECT")
Replace greater than with
how to get same value if its more than the actual value.
for eg: actual value is 100 and other value comes at 110 so i want 110 should display as 100.
Hy I Make A Inventory Sheet And I Want To Highlight That Row Where Quantity is 0.
Hi,
I am preparing a Excel LOG template for my document submissions and tracking. Documents submitted will have revisions & approvals based on comments from client. I want to prepare an excel LOG with functions to return Doc status whether it is Approved (A), Approved with Comments (B), Resubmit (C), Reject (D), Under Review (UR). I tried with some combinations of IF function & CHAR functions.
CHAR function
=CHAR(MIN(CODE(N12),CODE(V12),CODE(AD12),CODE(AL12),CODE(AT12)))
CHAR Functions is working fine with one limitation, after getting status (C) for submitted document, while I resubmit and enter (UR) in Rev. status cell, the value is not returning. I guess with a combination of IF & CHAR function it should work.
Please help me to solve this.
Hi there,
I need a formula that says:
If column M says 'Edinburgh  Leeds' then N will say 163 miles.
Then if column M says 'Edinburgh  Manchester' then column N will says 175 miles.
I have to do this for many different distances but they two are just an example.
Hello
I need a formula for if() i have 9 cells in a row for montly sales example 2 4 6 7 0 3 5 8 3 {yes}
I want to use =if to see if there is a 0 in the row to say yes or no
Hi L,
Try to formula below.
=IF(COUNTIF(A1:A10,0),"YES","NO")
=IF(OR(ISBLANK(N9),OR(ISBLANK(P9),ISBLANK(O9))),,(N9*(P9O9))*100) I want to add a function so that it will only multiply by 100 if Row L9 to L123 says Debit or Credit. so if it does not show debit or credit it will put the number instead.
Hi Stephen,
=IF(OR(L9="DEBIT",L9="CREDIT"),(N9*(P9O9))*100,(N9*(P9O9)))
Formula above only checks per row. You get a value per row
=IF(OR(COUNTIF(L9:L123,"DEBIT"),COUNTIF(L9:L123,"CREDIT")),(N9*(P9O9))*100,(N9*(P9O9)))
Formula above checks the whole range of L9:L123 if it contains debit or credit.
If *100 is not there it works for stocks, with the * 100 it works for options.
Hello! I am having trouble writing a formula.
I need the date(that is not final) in C22 to project 107 weeks out and then compare that new date to 12/31/2024 and pick the earlier of the two. This new date would automatically go to cell c32.
I appreciate any help. Thank you
Hi Mallorie,
Please try the formula below.
=IF(C21<C22,C21,C22)
You might have to format the columns to be shown in the date format.
Thank you works like a charm.
Hello,
I have 2 tabs in one spreadsheet. On tab 1, I want the cell to change to 'Complete', if the column range on tab 2 are all answered 'Yes'. Otherwise, I want it to say 'Incomplete'. The answers for column on tab 2 are either "Yes" or "No". If there is even one 'No' in that column, I want the column on tab 1 to pick it up.
The following formula is what I've tried and it is not working, although I get no error.
Where tab 1 is named "Sold' and tab 2 is named 'SoldDetails'. Thanks for any guidance. Perhaps I should be using CountA to determine cells that are either 'No' or 'NA', but any help is appreciated.
=IF(SoldDetails!C8:C21="Yes","Complete", "Incomplete")
Hi Carrie,
please try the formula below.
=IF(COUNTIF(SoldDetails!C8:C21,"NO"),"INCOMPLETE","COMPLETE")
Hi A, Perfecto! Works like a dream. Thank you very much.
I am working on an inventory thing and all I want is for the drop list I have created for the cells to subtract 1 from the total in another cell.
Example  If C3 is (I select an item from the drop down) then subtract 1 from V3.
I just have a floor plan that I have layed out for deployed monitors and want to be able to select from the drop down a Samsung monitor for example and then it take 1 away from the total I have in another cell. Should be super simple however I can't get it work with the IF statement.
I am trying to calculate a commission for realtors here in BC. We have a tiered structure here which is as follows
7% on the first 100k
2.5% on the balance.
This is then further broken to reflect
3.22% on the first 100k and 1.15% on the balance (agent representing buyer)
3.78% on the first 100k and 1.35% on the balance (agent representing seller)
I want to be able to put a client name in a cell and following cell the type of transaction (buyer or seller) and have a cell trigger a calculation for the above commission level when the word seller or buyer appears in the cell.
Hi
can you help me to find a value
if A1,B1,C1,D1,E1 have values like that
0, 0, 2, 0, 0
How we found this value
C1 value not specified its Formula based value
Regards
IF THIS CELL IS LESS THAN OR EQUAL TO 30 COPY THE VALUE OF THIS ROW,IF NOT,ZERO,
Hi Ron,
=IF(A1<=30,A1,0)
The site may have issues showing the formula. If the above is broken, try the one below.
=IF(OR(A1=30,A1<30),A1,0)
I need a function to determine if age greater than 23, and should return the text Yes if true and No if false.
=IF(A1>23,"YES","NO")
Hello,Can you please help me with this?=IF(AM4=AM6,IF(AK4="MED""NWRK""CLL""RQO""OOS""WCMP",0,AK4AO4AP4AQ4)AL4)
This formula does what I want when there is data in AM6 but if AM6 is blank it returns #Value. This happens only when MED, NWRK, RQO etc is typed in AK4. Why?
Thanks in advance for your time.
Hello, can you please help me with this?=IF(AM4=AM6,IF(AK4="MED""NWRK""CLL""RQO""OOS""WCMP",0,AK4AO4AP4AQ4)AL4)
If there is data in AM6 I get the result I want. If MED, NWRK, CLL etc is entered in AK4 and there is no data in AM6 I get #value. I would like a blank cell. This was working when it was just"MED" and had not added the others.
Thanks for your time.
Hi, The concept you put up is conflicting, please narrate the job properly in order to provide you with the correct format.
i have a formula to work out if stock is short over or equal how do it add the equal =if (c2>b2;"over";"short")
=IF(C2=B2,"EQUAL",IF(C2>B2,"OVER",IF(C2<B2,"SHORT","")
Amended =IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ")
 =IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ") 
=IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ")))
Please help,
what am I doing wrong? the cell just returns what is in AT2 regardles of what is in C9
=IF($C$9="1",IF($C$9="2",IF($C$9="3",IF($C$9="4",IF($C$9="5",IF($C$9="6",IF($C$9="6",IF($C$9="8",IF($C$9="9",IF($C$9="10",BL2,),BJ2),BH2),BF2),BD2),BB2),AZ2),AX2),AV2),AT2)
Thanks.
never mind fixed it
=IF($C$9=0,"",IF($C$9=1,AT2,IF($C$9=2,AV2,IF($C$9=3,AX2,IF($C$9=4,AZ2,IF($C$9=5,BB2,IF($C$9=6,BD2,IF($C$9=7,BF2,IF($C$9=8,BH2,IF($C$9=9,BJ2,IF($C$9=10,BL2)))))))))))
=IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," "))) done
Hi, i would like to ask you, if a cell has a value how can i ask to put an other value to the next cell which is empty?
Thank you!
Hi Antigoni,
You'll need an if statement similar to below:
=IF(A1="VALUE","TRUE","FALSE")
Hi,
I have two tables with different data. I want to create a new table, extracting information from both tables (some of the data in the first table may not be in the other table but I want to be shown all of them), because I want to find the difference from both tables.
Thank you,
You can learn more about find the difference from tables in Excel in this article on our blog.
If I understand your task correctly, our Ablebits Compare Sheets for Excel may help you solve it in a few clicks.
Hi,
What function would I use to say "If there is a value in this range of cells, input "yes", otherwise input nothing"
Hi Sony,
You can use IF together with the COUNTA function that counts the number of cells in a range that are not empty. For example:
=IF(COUNTA(A1:D1), "yes", "")
Hi I am trying to create a formula in excel for a loan officer. I want it so that commissions are automatically populated based on the type of "lead" it is.
I have column 'a' filled with the lead type, including either "selfgen", "branch", or "corporate"
I have column 'b' filled with the loan amount
I want column 'c' to autopopulate with the commission.
If a "selfgen" lead, then it would = cell 'b' * .015
If a "branch" lead, then it would = cell 'b' * .006
If a "corporate" lead, then is would = cell 'b' * .0035
Thank you!
Hi Lesley,
Try the formula below.
=IF(A1="SELFGEN",B1*0.015,IF(A1="BRANCH",B1*0.006,IF(A1="CORPORATE",B1*0.0035,"")
Hi! I need this formula: =if((C1>0 and <30);A1;0). How to use formula in brackets to compare numbers. I need to be if C1 is between 0 and 30? Thank you!
Hi Nadia,
Try the formula below
=IF(AND(C1>0,C1<30),A1,0)
Great. Thank you! :D
Hi,
Please help me with a formula for the following:
I have 2 columns: HR100 101001
HR102 101001
HR103 101001
Based on the value in the 1st column I want to return the value from the second column. But see, some has the same number. So if I am using Vlookup, if you have the same value will return N/A. Please help me. Thanks
Hello Georgiana!
If there are some repetitive values in the column you're working with, you should sort it accending beforehand and set the Range_Lookup parameter as 1 or TRUE. Besides, it would be a good idea to place an unary operator () so that Excel considers it to be a number, not a text string. Having said that, the formula should look like the one below:
=VLOOKUP(F3, $B$1:$D$5, 2, 1)
Alternatively, there is a combination of INDEX + MATCH functions that may be helpful as well. You may find more information on this topic here.
=IFS(A1 = 5, "Excellent", A1 = 4, “Very Good”, “Good”) has a syntax error/is incorrect
What is the correct formula.
Hello Kedevin!
Please use the following formula
=IFS(A1 = 5, "Excellent", A1 = 4, “Very Good”)
=IF(B2C2,"OK" “Wrong amount”) has a syntax error/is incorrect
What is the correct formula.
Hello Kedevin!
If I understand your task correctly, the following formula should work for you:
=IF(B2C2>0,"OK","Wrong amount")
=iferror(if(c6="",1,match(c6,holidays,0)),0)
Using the above code I can't find the answer. I used this program for the holidays on the helper shit, but the holidays are not being shown in attendance sheet. What do I need to do?
Hello Nitin!
For me to be able to help you better, please specify what the "holidays" named range should contain and what should be in cell C6.
Thank you.
Hi Svetlana,
could you advise any "If" combination to read a range of text and return a specific value?
Example: there are 10 test results and there is one overall result. If 9 test result are "passed" and 1 is failed then the overall result to be marked "failed". If all 10 test results are "Passed", then the overall result to be marked "Passed".
Thanks
Shobert
Hello Shobert!
If I understand your task correctly, the following formula should work for you:
=IF(COUNTIF(A1:A1,"Failed")>0, "Failed","Passed")
I hope it’ll be helpful.
HI.
I want to know is it can insert icon to replace formula return ex:IF(B2>=A2,"😩",IF,"😁" like this?
If can, please draft the formula.
HI.
I trying to insert icon to replace formula return ex:IF(B2>=A2,"😩",IF,"😁" like this?
If can, please draft the formula.
Hello Ahmad!
You can see symbols that can be inserted in an Excel cell via Insert –> Symbol. There you can also see a code of a symbol. This code can be used with the CHAR function. For example, =IF(A2>B2,CHAR(74),CHAR(76))
Please remember that you should apply the Wingdings font to the cell where you insert the formula so the symbols display correctly.
I hope it’ll be helpful.
I am coming with my problem directly. In a particular cell I enter something like this 20AAACT2520C1ZT. Now the first two digits of this number i.e. '20' represents state code. I want the adjacent cell to automatically recognize the state code and display the name of the state. Is it possible to do so???
Hello Debarun!
You can extract the first two characters using this formula:
=LEFT(A1,2)
After that, you can use this code with the IF function:
=IF(LEFT(A1,2)="20", [to show if YES], [to show if NO])
Or use the VLOOKUP function for pulling the state name based on the code. You can find more info about VLOOKUP here: https://www.ablebits.com/officeaddinsblog/2014/07/17/excelvlookuptutorialbeginner/
If there is anything else I can help you with, please let me know.
Hi there. I am trying to create a formula based around this information. Let me know if it is possible.
If D1 is less than 3 than or equal than the value should be 7. If D1 is greater than 3 but less than 7 than the value is 10. If D1 is greater than 7 than the value is 16.
Thanks
Hello Sarena!
Please use the following formula
=IF(D1>3,IF(D1<=7,10,16),7)
If there is anything else I can help you with, please let me know.
Thank you! I am just starting to work outside of basic formulas.
why won't this work?
=IF(A1="Departmental Manager",20000,IF(A1="Departmental Supervisor",18000,IF(A1="General Employee",15000,IF(A1="Cleaner",8000))))
Hello André!
Please describe your problem in more detail. The formula is bringing back the results of calculations. Thus, it is working. Perhaps, you would like it to work differently. What results would you like to get? How do they differ from the results which your formula is bringing back?
Hi pls I need an urgent help hear pls. You are to write a program that will accept the student name and score then automatic "call d name of the student with congratulation if he/she score above 10. A1= John, B1= 12 C1= if(B1>10, A1, Congrate
hi, everyone, can anybody hope you are doing well.
I have a question of how we can use the IF function for the following argument.
if student mark is 15 and over 15 will be reward 2
If student mark 10 or above 10 will be Rewarded 1
if student mark is 9and 0.5 will be rewarded 0.5
I did the following but it doesn't work.):
=IF(G15>=15,2,IF(AND(G15>=10),1,IF(G15>=9,0.5,0)))
Hello Sadaf!
It is better to write your formula in the following way:
=IF(G15>=15,2,IF(G15>=10,1, IF(G15>=9,0.5,0)))
However, it is not quite clear to me what exactly is not working in it. Please explain what this sentence means: "if student mark is 9and 0.5 will be rewarded 0.5". Please specify what the mistake is, in your opinion, and I will try to help you.
Hello,
Can you please help me with the following formula?
469,581,409.75 CR
271,160,113.76 DR
assume numbers are A column and CR/DR in the B column.how can we get the  values in 'DR' to the C column.
Hello Sumedha!
Unfortunately, your question is not clear enough.
Is 469,581,409.75 in Cell A1?
What result would you like to get in Cell C1?
Please describe the problem in more detail.
Hello I help to create an IF statement...
=IF(D14>0 then D14*D15 place value in D16 otherwise leave blank.
I know I'm missing something just can't figure out what it is
Can u please help me for following date formula
=if(A2:A30 01012018 to 31012018,sum(B2:B30)
how i write please?
Hello Nayon!
Please try the following formula:
=SUMPRODUCT((A1:A10>=DATE(2018,1,1)), (A1:A10<=DATE(2018,1,31)), B1:B10)
Hope you’ll find this information helpful.
Hi Alex,
Really appreciate your explanations and I wasn't aware of the "" notations and it is quite handy.
Thanks for the great work the Ablebits Team does!
Say for eg: IF(A3>A2, "Increase", "Decrease") and i want to fill the background for increase with green and Decrease with red.....Can it be done using IF statement other than conditional formatting option
Hello Shirish!
If I understand your task right, you want to change the cell background using a formula, not applying Conditional Formatting.
Unfortunately, an Excel formula cannot format the cell  it can only return a value. Your task can be solved either with Conditional Formatting or VBA.
Hi Ablebits team
I am trying to run an IF function to get a value in a third cell based on cells 1 and 2. If Column A has a "NO" and Column I is blank Column J should have a "NO"  If you go to my Google sheet link it is easier to understand: https://docs.google.com/spreadsheets/d/1s8uSJdkghRubcJfFFQdlElH_a2wXfC0PbZ7QW_zvBl8/edit#gid=0
Thank you!
Vlad
Hello Vladimir!
I’m sorry but your task is not entirely clear to me.
"If Column A has a “NO” and Column I is blank Column J should have a “NO” '"  formula
=IF(AND(A6="No",I6=""),"No","")
But you have a completely different more complicated formula written in the table.
For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.
I am trying to use an IF formula.
 I have Sheet 2 col DL concatenated text that matches col o2 on sheet 1
 Sheet 2 col M matches Sheet 1 col D
 I need to match these then if in col CV = 'Open' on sheet 2 I then need to find the latest date in sheet 2 col CX and bring that back if equals 1. critical or 2. Severe if not critical.
How do I combine match and If please help
This worked below but for another query but if I chance AM to CV if fails.
=IFERROR(INDEX('sheet 2'!$AM$1:$AM$7000,MATCH($O2&$D2,'Sheet 2'!$DL$1:$DL$7000&'sheet 2'!$M$1:$M$7000,0)),"")
Hello!
Unfortunately, your question is not clear enough. Your description is very confusing. For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.