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 9. Total comments: 2999

  1. Good day,
    If a cell has no amount - in need to make it zero.
    I use to use this formula and now it is not working:
    =if(d4"",0,D4)

  2. Hi, I am struggling to set the formula for the following:
    Column B needs to read May if the value in column C is between May 1 and May 31, 2020.

    1. Hi Candice,

      Assuming column C contains dates, you can extract month to column B with this simple formula:
      =TEXT(C1, "mmmm")

    2. Hello Candice!
      Please try the following formula:

      =VLOOKUP(MONTH(C1), {1,"January";2,"February";3,"March";4,"April";5,"May";6, "June";7,"July";8,"August";9,"September";10,"October";11, "November";12,"December"}, 2,0)

      I hope it’ll be helpful.

      1. Hi,

        Similar Question with the dates. I have a source data and I want to get the dates based on cut-off.
        1. Row 1 . May 23 - June 20, 2019. Payments made was June 21 (Column C). I would like my Column D to read July.
        2. Row 2. April 23 - May 22, 2020. Payments made was April 25 (Column C). I would like my Column D to read May.
        Can I use "IFS Function" for this? Thank you in advance.

        1. Hello Philip!
          I cannot recommend you which function to use - IF or IFS, since you did not say anything about the conditions under which the formula will determine the month in column D. If there is one condition, then you can use IF. But this is only an assumption. No information needed to answer.

  3. Hi,

    I am trying to do an IF then formula but having trouble coming up with it. I have 4 different columns that I mark with an X dependent on which patient meets which column. However I want an additional column (if,then column) that will calculate if I have put in X in any of the 4 columns in that row and to mark it by yes if there is an X or no if there is not. Can you assist with this?? Thank you!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(COUNTIF(A1:D1,"X") > 0,"X","")

      I hope it’ll be helpful.

  4. Hi I am trying to create a formula that allows me to quickly match the same data that appears in 2 columns

    so if text in column A has a match with text in column b no matter what row it is displayed in, display OK can you please help?

  5. I need an IF statement that determines the unfilled up cell.
    ex.
    if a number of cells like 05,P5,Q5,R5,S5,T5,U5,V5,W5 which belong to step 1, step 2 and soon are blank I need to specify which step is missing.

    1. Hello!
      If I understand your task correctly, please try the following formula:

      =SORT(IF($O$5:$W$5="", COLUMN($O$5:$W$5)-15,""), ,1,TRUE)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  6. my question is that,
    my formula is accurate, but i want that if in my selected cell no any data in formula cell show that " - " character,
    mostly when your selected cell not have data , your formula cell show that " #value! "
    I want that show as like " - ", with out effected the formula,
    please help me in this task.

  7. I am trying to use =IF... If the text in C48 equals a picklist (five different products) I would like cell E48 to equal the dollar value associated with the picklist name.

      1. Thanks

  8. Hi I would like to create a formula that gives out 100% if "Text1" or "Text2" but a variable percentage that I put in on my own if "Text3" or "Text4". I have at this point reached this formula which does not seem to work: = IF(OR(A3="Text1";"Text2";);100%;"")
    Excel complains and says that my conditions for OR formula needs to be a number, but my conditions are in text so I dont know what to do, so maybe another forumla similiar to OR but you can use text. Maybe some sort of If and Else formula needs to be used here but I dont know how.

    1. Hello Edgar!
      Please try the following formula:

      = IF(OR(A3="Text1",A3="Text2"),100%,"")

      I hope it’ll be helpful.

  9. Hi Svetlana
    Please if I have a formula like this:G1=A1&B1&C1&D1&E1&F1 the value of the cells are in words not numbers.the formula actually gave me the my desired results but how can I give a demacation like " , " in between each words. Secondly, how can I make the results to centralized in the cell.
    Thanks.

    1. Hello Enity!
      If I understand your task correctly, the following formula should work for you:

      =A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1

      or

      =CONCATENATE(A1,"",B1,"",C1,"",D1,"",E1,"",F1)

      To center the text, use the menu Alignment - Center.
      I hope it’ll be helpful.

      1. How to I extract text before the Qty ? suppose I just want to extract "bought" from 1st line
        Bought 300 CODX @ 10.3
        Sold 300 CODX @ 10.5
        Sold Short 300 TWTR @ 24.7
        Bought to Cover 300 TWTR @ 24.857

  10. I am in if assistance. This is what I am attempting to do. I need to calculate the total amount (dollars) that meet delinquency days. For example, Column A is the amount, Column B is the days late, and return the total in other cells based of a time span. "0-29" in Cell A7, "30-59" in Cell A8, and "60-89" in cell A9

    1. Hello Thad!
      Please try the following formula:

      =SUMIFS(A1:A15,B1:B15,">0",B1:B15,"<30")
      =SUMIFS(A1:A15,B1:B15,">=30",B1:B15,"<=59")
      =SUMIFS(A1:A15,B1:B15,">=60",B1:B15,"<=89")
      I hope it’ll be helpful.

  11. I need a simple formula, but cannot seem to figure out the answer. I need an IF statement. If there is text in either C8 or C9 greater than 0, then ((c8+c9)/c5) then show -. So far I have =IF(C8>0,((C8+C9)/C5),"-") which works if I have text in c8, but I cannot figure out how to include c9. I may not always have text in C8 and still need the percentage to calculate if c9 has text too, Thanks

    1. Hello Gabriel!
      If I got you right, the formula below will help you with your task:

      =IF(OR(C8>0,C9>0),(C8+C9)/C5,0)

      1. Hi,
        If you have four categories like some, little, none and regular and you want them to prioritise them then how could you do that using IF function or VLOOKUP function?

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

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

  13. Can u please help me for following date formula
    =if(A2:A30 01-01-2018 to 31-01-2018,sum(B2:B30)
    how i write please?

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

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

  14. why won't this work?
    =IF(A1="Departmental Manager",20000,IF(A1="Departmental Supervisor",18000,IF(A1="General Employee",15000,IF(A1="Cleaner",8000))))

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

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

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

      1. Thank you! I am just starting to work outside of basic formulas.

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

    1. 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/office-addins-blog/excel-vlookup-tutorial/
      If there is anything else I can help you with, please let me know.

  17. HI.
    I trying to insert icon to replace formula return ex:IF(B2>=A2,"?",IF,"?" like this?
    If can, please draft the formula.

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

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

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

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

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

  20. =IF(B2-C2,"OK" “Wrong amount”) has a syntax error/is incorrect
    What is the correct formula.

    1. Hello Kedevin!
      If I understand your task correctly, the following formula should work for you:
      =IF(B2-C2>0,"OK","Wrong amount")

  21. =IFS(A1 = 5, "Excellent", A1 = 4, “Very Good”, “Good”) has a syntax error/is incorrect
    What is the correct formula.

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

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

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

    1. Hi Nadia,
      Try the formula below
      =IF(AND(C1>0,C1<30),A1,0)

      1. Great. Thank you! :D

  24. 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 "self-gen", "branch", or "corporate"
    I have column 'b' filled with the loan amount
    I want column 'c' to auto-populate with the commission.
    If a "self-gen" 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!

    1. Hi Lesley,
      Try the formula below.
      =IF(A1="SELF-GEN",B1*0.015,IF(A1="BRANCH",B1*0.006,IF(A1="CORPORATE",B1*0.0035,"")

  25. Hi,

    What function would I use to say "If there is a value in this range of cells, input "yes", otherwise input nothing"

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

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

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

    1. Hi Antigoni,

      You'll need an if statement similar to below:

      =IF(A1="VALUE","TRUE","FALSE")

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

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

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

    1. ---=IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ")))----

    2. Amended =IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ")

      1. -- =IF(C2=B2,"Equal",IF(C2>B2,"Over",IF(C2<B2,"Short"," ") --

    3. =IF(C2=B2,"EQUAL",IF(C2>B2,"OVER",IF(C2<B2,"SHORT","")

  30. Hello, can you please help me with this?=IF(AM4=AM6,IF(AK4="MED""NWRK""CLL""RQO""OOS""WCMP",0,AK4-AO4-AP4-AQ4)-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.

    1. Hi, The concept you put up is conflicting, please narrate the job properly in order to provide you with the correct format.

  31. I need a function to determine if age greater than 23, and should return the text Yes if true and No if false.

    1. =IF(A1>23,"YES","NO")

  32. IF THIS CELL IS LESS THAN OR EQUAL TO 30 COPY THE VALUE OF THIS ROW,IF NOT,ZERO,

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

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

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

  34. =IF(OR(ISBLANK(N9),OR(ISBLANK(P9),ISBLANK(O9))),,(N9*(P9-O9))*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.

    1. Hi Stephen,
      =IF(OR(L9="DEBIT",L9="CREDIT"),(N9*(P9-O9))*100,(N9*(P9-O9)))
      Formula above only checks per row. You get a value per row
      =IF(OR(COUNTIF(L9:L123,"DEBIT"),COUNTIF(L9:L123,"CREDIT")),(N9*(P9-O9))*100,(N9*(P9-O9)))
      Formula above checks the whole range of L9:L123 if it contains debit or credit.

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

    1. Hi L,
      Try to formula below.
      =IF(COUNTIF(A1:A10,0),"YES","NO")

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

    1. Hi Barbora,
      Please see below.
      =IF(OR(B6"",B7"",B8""),"SECTION BOOKED","SELECT")

      1. Hi Barbora,
        Replace "greaterthanlessthan" with the operators
        =IF(OR(B6 greaterthanlessthan "",B7 greaterthanlessthan "",B8 greaterthanlessthan ""),"SECTION BOOKED","SELECT")

        1. Replace greater than with

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

    1. Hi Harold,
      =IF(A1="X",10,IF(B1="X",5,IF(C1="X",1,""))

      Is that what you're looking for?

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

    1. SOLVED - =IF(D6=D6,IF(B6="OFF",0,B6)-C6) - Thanks anyway.

  39. Can anyone help

    =IF(A4="Month"&B4="US:W2 Salaried",173.33)

    why it is returning false statement

    1. Hi Ankur,

      =IF(AND(A4="Month",B4="US:W2 Salaried"),173.33,"")

      Was that what you were trying to do?

  40. Could you please help, I want to write if E5 is greater or smaller than 0 enter that amount in C6

    1. Hi Chris,
      The formula below should work.
      =IF(OR(E5>0,E5<0,),E5,"")

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

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

  42. Hi Svetlana,

    I would like to return a value of "No" in B1 if A1 is either "0" or "na".
    Is this possible?

    Thanks!

    1. Hi Brett,

      Sure. Please use this formula for B1:

      =IF(A1="", "", IF(OR(A1=0, A1="na"), "No", ""))

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

    1. Hi Lee,
      =IF(F2="T19","A19","")
      Is that what you meant?

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

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

    2. i have this as my current =IF(G5>10,"2",IF(G5<=10,"1",IF(G5<0,)))

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

      2. =IF(G7>10,2,IF(AND(G70),1,IF(G7=0,0,"")))

        Hope that helps.

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

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

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

    1. Hi Jason,

      It's not clear how you calculate the percentage of completion, but the idea is to count non-empty 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, "")

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

    1. That formula works except for when the value is "C".

      Change the "-" into "=".

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

    1. =IF(Sheet2!B2=0,"",Sheet2!B2)
      That should work.

      1. Thank you! Works like a charm!

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

    1. =IF(OR(A1="a",A1="b",A1="d"),"closed",IF(A1="c","open",""))

      Should work for your formula.

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

    1. Hi James,

      To count cells with text, you can use the COUNTIF function with "*" as the criteria. For example:
      =COUNTIF(C1:C9, "*")

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)