Comments on: How to use IF function in Excel: examples for text, numbers, dates, blanks

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

  1. 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

  2. Could someone please help me describe how writing an incorrect IF statement might produce the wrong data??

  3. 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.

    1. 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="".

  4. 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

  5. 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!

    1. 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", "")

  6. 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

  7. Please help to apply:

    If A1>48, “5 Years” A1>36, “4 Years” A1>24, “3 Years”

  8. 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

  9. 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).

  10. 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

  11. 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.

    1. Hi Joe,

      Here you are:
      =IF(ISNUMBER(SEARCH("bird",A1)), A1, "")

  12. 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.

  13. 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

    1. 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")

  14. 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

    1. Hi Agana,

      Here you go:
      =IF(A1>0, B1*C1, 0)

  15. 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

  16. 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

  17. 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

  18. =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

  19. 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

  20. 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?

  21. 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?

  22. 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

  23. 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

  24. 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))

    1. 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))

  25. 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

    1. Hi Rob,

      You can use the following formula:
      =IF(A3>50, A3*A2+A1, A1)

      1. 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

  26. 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,

    1. Hi Daniel,

      Try this one:
      =IF(MID(M2,2,1)="0", "rural", "city")

  27. 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

  28. 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

    1. Hi Ismaili,

      Just add one more IF function to check for a blank cell:

      =IF('Sheet1'!G12="";""; IF('Sheet1'!G12="WBGELD";"C";"A"))

  29. 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

    1. Hello Razzouk,

      Here's the formula for C1:

      =IF(A1*B1>2, A1*B1, 2)

  30. 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

    1. Hi Tegan,

      Here you go:

      =IF(OR(A1<=0, B1<=0), 0, A1+B1)

  31. 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.

    1. 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)

  32. 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.

    1. 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")

      1. Svetlana,

        Thank you very much.

        Alvin

        1. Svetlana,

          Now the employee wants it to be time, can you show me how to convert the formula to time?

  33. 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,""))

    1. Hi Nick,

      You put it like this:

      =IF(G6="HPHE",P10, IF(G6="OFA",P11,""))

  34. 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?

  35. =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.

    1. 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).

  36. 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

    1. Hello Prabhu,

      Here you go: =IF(A1="US", 1000, IF(A1="indiab1", 2000, ""))

  37. 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)

  38. 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.

  39. 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

  40. 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.

    1. 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)

  41. 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.

  42. 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!

    1. Hi Rebecca,

      To add up cells that meet a certain criteria, use the SUMIF function like:
      =SUMIF(D4:D103,">1",A4:A103)

  43. 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.

  44. 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")

    1. 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")))

  45. 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

    1. 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".

      1. Works perfectly!!!!

        You saved my life!!

        Thank You

  46. 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.

    1. Hi Karissa,

      You can use a formula similar to this:
      =IF(AND(B1="x", C1="y"), A1, "")

  47. hi..
    this lesson was more helpful for me to study regarding functions of MS excel.thanks a lot all of you..

  48. 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.

    1. Hi Chris,

      You can use a nested IF formula like this:

      =IF(AND(AE6="o", AE20="o"), 25, IF(OR(AE6="", AE20=""), 0, ""))

      1. 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!

        1. Hello Chris,

          Please use:
          https://www.ablebits.com/office-addins-blog/excel-and-or-xor-not-functions/

  49. 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

  50. 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.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)