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. Continue reading
by Svetlana Cheusheva, updated on
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. Continue reading
Comments page 115. Total comments: 4830
Hello,
Just a quick one.
I need some assistance with this...
=IF(F6="A", H6)
For Example
F G H
6 A =IF(F6="A", H6) Hello
7 Goodbye
I need the if command to also pick up other values too
Could someone please help me describe how writing an incorrect IF statement might produce the wrong data??
Hi!
Good day! I was trying to link the formula from one cell to another.
sample: =IF(D14=0,"Required Field","") ((This is under E14)
Formula cannot be used id created under D14.
Or are there any other formula that can produce the same answer which is "Required Field"
Your help is much appreciated.
Thank you.
Hi Jenah,
>Formula cannot be used id created under D14.
Absolutely right. You cannot enter a formula in a cell that contains other data. If you enter it in E14, the formula should work fine.
Please note that D14=0 checks D14 for 0. If you want to check for an empty cell, use D14="".
hiiiii
i want to place a specific word in all the row.but some row is having a specific word
for eg. 1 name having dr.sameer
2 name having kanti
so i want dr. in all name which is having word dr in it or not
Hi!
Good day! I am currently working on an Application Form. I don't want them to missed any required fields. Is there any formula that we can use to prevent them in missing any fields or "this is a required field" will prompt on each missed out cell.
=IF(D6=" "," ","Required Field")is not working. Help please!
Thank you!
Hi Jenah,
If you want the formula to return "Required Field" when D6 is empty, you should put it the other way round:
=IF(D6="","Required Field", "")
Hi there,
I want formula for this
if in cell A1 I write "MISC CON_Sep15_vat12"
in cell B2 i have to write VAT AMOUNT.IN other words
if THE cell A1 contains THE WORD VAT THEN only I can enter any numerical value in THE CELL B2
Please help to apply:
If A1>48, “5 Years” A1>36, “4 Years” A1>24, “3 Years”
Hi Svetlana:
My formula is: =IF(D$2>=$B$1,D3-D4-D5,0)
D2 is "Q1'17"
B1 is "Q3'16"
The result based on above formula is "0", which I know it should be the calculation based on D3-D4-D5. Would you please help correcting the formula so it can recognize that Q1'17 is greater than Q3'16.
Thank you
Hi Diana,
In all Excel formulas, you need to enclose text strings, not numbers, in double quotes, like "x".
So, here's the formula as per your conditions:
=IF(A12="X"; 0; IF(ISNUMBER(A12); 1; ""))
If A12 is neither "x" nor number, the formula will return an empty string (blank cell).
Hi Svetlana. Please heeeelp me with a formula. So, if in my table, I have X, I want the result to be "0"and if in the table appears a number, the result to be "1". I've tried:
IF(A12=X,"0";"1") but it doesn't work. It gives me NAME as result
Hi Svetlana,
I am looking for a formula that will look across a range of values to see if any of the values contain a specific value (note: the values will never be exact) and then present the match in another range of cells.
For example: in column A I have a list of songs:
A1: free bird
A2: you really got me
A3: the pretender
A4: the bird is the word
I'd like to look across the range A1:A4 and then display the name of each song ONLY if it contains the word "bird" in it. So in column B for example: B1: free bird and B2: the bird is the word would display.
Hi Joe,
Here you are:
=IF(ISNUMBER(SEARCH("bird",A1)), A1, "")
I need the formula if A2=50, 51, 52, 53, 54, 55, 56, 57, 58, 59 then result will be 1, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9 respectively.
Hi Svetlana!
Please help me with my formula. This is how it goes: If A1 to A5 is greater than or equal to 3 and less than or equal to 10, show "OK" but if not, show "NG". This is the formula I have come up with but it doesn't seem to work: =IF(AND(A1:A5>=3,A1:A5<=10),"OK","NG")
Thank you in advance and I'm looking forward for your reply.
-Paul
Hi Paul,
The IF function does not work correctly with ranges, you need to reference each cell individually, like this:
=IF(AND(AND(A1>=3,A1:A5<=10), AND(A2>=3,A2<=10), AND(A3>=3,A3<=10), AND(A4>=3,A4<=10), AND(A5>=3,A5<=10)), "OK","NG")
Alternatively, you can use a more compact array formula (remember to press Ctrl+Shift+Enter to enter it correctly):
=IF(SUM((A1:A5>=3) * (A1:A5<=10))=5, "ok", "ng")
Hi There,
Want to multiply cell B & C if cell A is greater than 0. Anything less than 0 in cell A should return 0.
Thanks,
Agana
Hi Agana,
Here you go:
=IF(A1>0, B1*C1, 0)
have a formula i have been trying to fix -
=IF(AND(X16>99.9999%, Y16>99.9999%),(IF(TRUE,W33),IF(AND(X16<99.9999%, Y16<99.9999%),(IF(FALSE,W32)))))
what im wanting is the X16 + Y16 if both are true to show the info in cell W33 BUT if ether is False to show info in cell W32
Hi there,
In collum 'A', I have different categories. Collum 'B' and 'C' are both used for names, and collum 'D' is used for the value of each thing.
However, I need a total for each category.
I tried stuff like "=IF (Tabel1[Soort])=amp , =SOM(Tabel1[Prijs]) , ", but it doen't seem to work. Anyone who knows if Excel is capable of this?
All tips are offcourse apreciated.
Laurens
Hi Svetlana,
cold you please help me with below.
There are two rates for the same product. If i buy 10 units, my cost is 2 per unit. If i buy more than 10 units, then cost will be 2 per first 10 units and 3 per additional unit. I need to buy 16. What should be the formula.
thanks in advance
=IF(AND(E15:E222,"INS",E15:E222,"HTS"),"7"," 14")
HI,
I NEED A HELP WITH FORMULA. WITHIN A CELL,THE CONDITION IS, GET THE NUMBER 7 IF ALL THE CELLS WITHIN THE COLUMN APPEARS "INS" ON FILTERATION AND 14 IF ALL THE CELLS CONTAINS "HTS".
THANKS IN ADVANCE.
INS
HTS
HTS
HTS
HTS
HTS
HTS
HTS
INS
HTS
HTS
HTS
INS
HTS
HTS
INS
HTS
HTS
HTS
Hi,
I am using a pivot table which provides dates the future event is to occur. I need to display (in a cell outside of the pivot table) the date furthest in the future. I am using the formula
="First date "&text(max(date),"mm dd yyyy")
But, I keep getting the response of First date 01 00 1900.
What am I doing wrong?
Thanks in advance,
Alicia
HI! I need help with a formula. In a cell, the condition of the formula is either a plain number or percentage...how do I make the condition for that if the condition is a plain number will show or a percentage? I used the If nested function but when adding the percentage, it keeps saying "error"
What should I do?
hi, i need help in creating a logic formula
lets say i have 2 different formula, but both formula give the same value. and with the value i have obtain i needed to to the other formula. the question is, how can i use the value if one of the formula is blank?
Hi All,
Can anyone help me with this one:
If A1 says Vendor 1, A2 Vendor 2 so on and D1 says Vendor 1 then the B1 is ZZ and E1 is AA.
I need a formula that will match cells A1 and D1 then, if they are match/equal will proceed in matching the B1 and E1 cells.
Thanks
HI to all
can any one hlep meon this:
I have anumber in cel A1 250, I want to put in cel B1
If the number in A1 more than 180, add -360 to A1
Please send me a reply on my e-mail many thanks in advance
Please help!
I need a formula that looks at a cell.EG A1
If A1 says “card” or “mail order” then it needs to take the value off another cell (B1) and times it by 0.03. If however A1 says “defaults” it needs to take the value in B1 and times it by 0.015.
This is what i have so far: it doesn’t work :(
=IF(A11=”Card”,b1*0.03,IF(A1=”Mail Order,B1*0.03,IF(A1=””Defaults”,B1*0.015))
Hi Kattrina,
You just have 1 incorrect cell reference (A11 instead of A1) and a missing closing parentheses at the end of the formula. Once you fix this, the formula works just fine:
=IF(A1="Card",B1*0.03,IF(A1="Mail Order",B1*0.03,IF(A1="Defaults",B1*0.015)))
To make the formula a bit more compact, you can add the OR statement, like this:
=IF(OR(A1="Card", A1="Mail Order"), B1*0.03, IF(A1="Defaults",B1*0.015))
Hi, im looking for a formula that will help me with the below example:
A1=600
A2=8
A3=122
quantity over 50 for A3 to be multiuplied by A2, plus A1.
if A3 is 50 or under, to display A1 only.
please help.
thanks, Rob
Hi Rob,
You can use the following formula:
=IF(A3>50, A3*A2+A1, A1)
Please I want a formula that can count through cells like C2:F2 to check for grade “A1” -“C6” = pass but if there is “D7” - “H” = fail
Hi!
I don't know what you want to calculate, but you may find this guide useful: How to use Excel COUNTIFS and COUNTIF with multiple criteria. If this does not help, explain the problem in detail.
Hello,
I am looking for a formula that will return "rural" if the second character of postal is "0".
N5Z3J2
L5R3S6
L2M4G3
M2M3R1
N0G1L0
So far I have this :
=IF(ISNUMBER(SEARCH("0",M2)), "rural", "city")
Anyone knows how I can target the second digit only?
Thanks,
Hi Daniel,
Try this one:
=IF(MID(M2,2,1)="0", "rural", "city")
A B C D E F G H
Annual Q1 Q2 Q3 Q4 Annual Q1 Q2
1]3.8 1.08 2.06 0.15 1.1 1.1 1.08 0.89
2]3.8 1.08 0.98 -1.91 0.95 1.1 1.081 -0.183
Hello sir/Madam
I have a question in above situation.
1st condition is Annual & Q1 value be always match . but Q2, Q3,& Q4 value never be match.
if Annual & Q1 value mismatch then it should be an error, and if Q2, Q3, &Q4 match then it should be an error.
now i have try [if(A1=A2,"OK","error")]this formula. but this formula work only Column A & B (e:g Annual & Q1)but it does not work Q2,Q3,Q4
So, could you please help me in this matter....
Thanks
hello
I work in two sheet with if function
=IF('Sheet1'!G12="WBGELD";"C";"A")
that function is in second sheet and i like when the cell is empty in first sheet to have empty blank cell and in second sheet
thank you kind regards
Hi Ismaili,
Just add one more IF function to check for a blank cell:
=IF('Sheet1'!G12="";""; IF('Sheet1'!G12="WBGELD";"C";"A"))
Hello,
help me in a formula in when :
for example : a1*b1= in_cell_ c1
-am need if this number in cell c1>2.00 keep the cell c1 the same vale (i mean keep the result a1*b1 without change ).
-and if the number in cell c1<2.00 replace the cell c1 to 2.00 automatically
thank you
Hello Razzouk,
Here's the formula for C1:
=IF(A1*B1>2, A1*B1, 2)
I need a formula that will sum if the value in both cell a & b are greater than 0. If one or both are less than 0 I need the sum to be 0. So if I have a 4 in cell a and a 0 in cell b I need the sum in c to equal 0. If cell a and b are both 4 I need c to equal 8.
Is there a way to do this formula? Thank you
Hi Tegan,
Here you go:
=IF(OR(A1<=0, B1<=0), 0, A1+B1)
I am looking for a formula to show date from today in a specific cell.
So I want to put a date in a1, amount in b1 and I want c1 to always show TODAYS amount. Does that make sense? I provided a visual below.
Example: Amount today
Date Amount 57
9/25/15 57
I tried the following and it did not work
=IF(A1=TODAY(), "B1")
Any ideas would be great.
Hi VIKING,
You were on the right track, just remove the quotes surrounding B1 because they turn a cell reference into a text string:
=IF(A1=TODAY(), B1)
Hello Svetlana,
I want to use the IF logic for the following, but don't know how to set it up:
If B2=I, and H2=>15, I2 should =Yes
If B2=R, and H2=>30, I2 should =Yes
If B2=I, and H2=>45, I2 should =Yes
If any of these are present and H2 is < then I2 should =No
Hopefully, you can help me because this newbie is lost.
All of the logic is based on the Letter in B2, and the time in B3.
Hi Alvin,
Here's the formula for I2 as per your conditions:
=IF(OR(AND(B2="I", H2>=45), AND(B2="R", H2>=30)), "yes", "no")
Svetlana,
Thank you very much.
Alvin
Svetlana,
Now the employee wants it to be time, can you show me how to convert the formula to time?
I cant figure this out.
If G6 equals "HPHe" then I want it to enter the value of P10, but if G6 equals "OFA" then I want it to enter the value of P11
=IF(OR(G6="HPHE",P10,"")=IF(G6="OFA",P11,""))
Hi Nick,
You put it like this:
=IF(G6="HPHE",P10, IF(G6="OFA",P11,""))
Hi
I want to know the formula for subtracting the numbers in three columns with numbers in the third and second columns of the first row are less than the numbers of the third and second columns of the second row.
How can I write the formula for that condition?
=IF(B37="","",IFERROR(COUNTIFS(BASF_GLOBAL!$AI:$AI,"Recommended",BASF_GLOBAL!$D:$D,$B37),""))
Can anyone tell me what B37="," means when B37 is a vell containing text value.
Hi Richa,
"" is an empty string. So your formula reads as follows: when B37 is empty, return nothing (blank cell), otherwise return the result of COUNTIFS. If COUNTIFS returns an error, then also return an empty string (blank cell).
HI
i need to know the formula for
if A1 is US b1 value should be 1000 if A1 value is indiab1 value should be 2000.
The value is fixed
thanks
Hello Prabhu,
Here you go: =IF(A1="US", 1000, IF(A1="indiab1", 2000, ""))
IF(AND(C3=2014,C3=2015),SUMIF(Sales!$B$4:$B$544,C4,Sales!$Q$4:$Q$544)+SUMIF(Sales!$B$4:$B$544,C4,Sales!$AD$4:$AD$544),IF(C3=2014,SUMIF(Sales!$B$4:$B$544,C4,Sales!$Q$4:$Q$544),IF(C3=2015,SUMIF(Sales!$B$4:$B$544,C4,Sales!$AD$4:$AD$5440),"")))
AND I WANT ANOTHER TRUE THING,
SUMIF(Sales!$B$4:$B$544,C4,Sales!$Q$4:$Q$544)+SUMIF(Sales!$B$4:$B$544,C4,Sales!$AD$4:$AD$544)
hello ma'am
i want to know about excel formula for:-
if we mark "x" in d1 cell then shows entry of cell a1,b1,c1 into next sheet, please tell me the formula.
Need to get value
A column = Numbers
B column = Text 1
C column = Text 2
D column = Text 3
Need to get answer if Text 1 is equal to Text 1 then number have to be printed
Hello!!
I need help with this formula : =IF(COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, (Stock3) - 6)
This works perfectly but I need to add "Right Triple Track" into the same formula for entire column Barcode:Barcode
I tried =IF(COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, (Stock3) - 6),(Barcode:Barcode, "Right Triple Track") > 0, (Stock3) -6 but it didn't work.
Hello Martin,
If you want the Barcode column to contain both "Left Triple Track" AND "Right Triple Track" at the same time, then use the following formula:
=IF( AND( COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, COUNTIFS(Barcode:Barcode, "Right Triple Track") > 0) , (Stock3) – 6)
If it is sufficient for the Barcode column to contain either "Left Triple Track" OR "Right Triple Track", then use:
=IF( OR( COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, COUNTIFS(Barcode:Barcode, "Right Triple Track") > 0) , (Stock3) – 6)
Hi,
Please help me out with the excel formula for Gosi (government tax) calculation.
Gosi - =(Basic+HRA)*Gosi but I want add saudi and non saudi in the excel sheet. So if the column has saudi employee will calculate accordingly and if non saudi as per that.
Eg:-
Saudi Gosi Company Expenses - 12% and Saudi Gosi employee expenses - 10%.
Non Saudi Gosi company expenses - 2%.
And below are details and please show some formula for the same.
Nationality Basic HRA Gosi Comp Expenses Gosi Emp Expenses
Saudi Saudi 10000 5000 12% 10%
Jordan Non Saudi 7000 3000 2% No deduction
Yemen Non Saudi 3000 1000 2% No Deduction
Pakistan Non Saudi 2000 500 2% No Dedection
Thanks Hooping Soonest reply.
Regards.
samsher Hashmi.
Hi - I have an "IF" formula that I want to sum up but it is giving me "0" even when the "IF" formula's have a total.
=IF(D4>1,"1","") this goes from A4 to A 103, I want A3 to be =Sum(A4:A103)
What am I doing wrong? :(
Thanks!
Hi Rebecca,
To add up cells that meet a certain criteria, use the SUMIF function like:
=SUMIF(D4:D103,">1",A4:A103)
I have a table and a list box. I am attempting to write a formula that takes the list box value and changes the table values based on the list box, basically resetting the table.
The table is set up for people to enter values. Then they need to be able to 'clear' the table and start over. We are not able to use macros or vb.
HI,
KINDLY HELP ME IN I HAVE A RANGE OF DATES VARYING FROM 0 TO 60. I NEED TO CATEGORIZE INTO FOLLOEING GROUPS IF DATE RANGE IS BETWEEN 60 TO 30 IT SHOULD RESULT > 30DAYS, 15 TO 30 AS > 15 DAYS, 5 TO 15 AS > 5 DAYS AND 0 TO 5 AS < 5 DAYS..
=IF(OR(I25,"<5 DAYS",I2=30,">30 DAYS"),">DAYS")
Hi KARTHICKEYAN,
You can use a nested IF formula similar to this:
=IF(I2>30, ">30 DAYS", IF(I2>15, ">15 DAYS",IF(I2>5, ">5 DAYS", "< 5 DAYS")))
Hi I am unable to figure this out. I need a formula that says: If any of the cells Barcode:Barcode contain the term "1 In Production", then add the text "In Production" to corresponding [In Production]1" cell.
Many Thanks
Hi Martin,
Try a formula similar to this:
=IF(COUNTIF(A1:A10, "1 In Production")>0, "In Production", "")
Where A1:A10 is the range of cells to check for the term "1 In Production".
Works perfectly!!!!
You saved my life!!
Thank You
Hi,
I have 3 columns (A, B, and C) and I want to make a list of text of column A based on the conditions of column B and C. It is somewhat like "if B = "text" and C = "text" => copy the text in cell column A and paste to the new cell.
How is the formula for that?
Thanks.
Hi Karissa,
You can use a formula similar to this:
=IF(AND(B1="x", C1="y"), A1, "")
hi..
this lesson was more helpful for me to study regarding functions of MS excel.thanks a lot all of you..
I am looking for a formula that will compare 2 cells on a sheet, if they both contain the letter o then the number 25 is to appear in a third cell, if one or both cells are blank, then 0 is to appear. i.e. if cell AE6=o and cell AE20=o then cell AF6 is to say 25. If either cell AE6 or cell AE20 (or both) are blank then cell AF6 is to say 0. Please help, thank you.
Hi Chris,
You can use a nested IF formula like this:
=IF(AND(AE6="o", AE20="o"), 25, IF(OR(AE6="", AE20=""), 0, ""))
Thank you. I am also looking for a formula that will post a symbol if 1 of 2 different cells = a value of 1, and fall within a of a number generated in another cell. Here is the formula that I am trying to use: =IF($E$7=1,IF(AND($L$18>=190,$L$18=160,$L$18<=164),"•")),"")))
Let's say that this formula is in cell A1, and when E7 is "activated" by equaling 1 a random number is generated in cell L18. If the number in that cell falls between or equals 190 and 194, I want a • to appear in cell A1, if not, then blank (unless the following formula is true). In the same cell A1, when D6 is "activated" by equaling 1, a random number is generated in cell L18, and if that number falls between or equals 160 and 164, I want a • to appear in cell A1, if not then blank (unless the other formula is true). Both E7 and D6 will never be "activated" at the same time. My current formula returns a #VALUE report in the cell.
Thanks in advance!
Hello Chris,
Please use:
https://www.ablebits.com/office-addins-blog/excel-and-or-xor-not-functions/
please suggest , if i select any month i need date wise in row
If May is selected then date 1 to 31 ,Sept 1 to 30 and for leap year feb
Hi,
I want to set up a formula that calculates if an invoice is "overdue" or "OK".
I have my Invoice Date Raised column, I have a Due Date column and a STATUS column which I currently enter manually. I want a formula that will change a the STATUS field as the Invoice date is hit/over. Does anyone know if this is possible and if so how? Im guessing I need a field to show the current date?
Michelle.