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 30. Total comments: 4822

  1. DEAR SIR,

    1. amount 15000 below amount fix 1800 amount 30 days but less days amount per day less
    2. 15000 and above amount fix 12% amount
    please share excel formula

    1. Hello!
      I’m sorry but your task is not entirely clear to me.
      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.

  2. I select a date from cell Q33 ( 04-May-2020), and in cell R33 add some duration to it from either P34, or Q34 and or R34 (Year, month and day respectively). In this case 0 years (P34), 0 months (Q34), 200 days (R34) which gives me 16-Nov-20 in R33. The date so arrived at in R33 should not be greater than year end date i.e, 31-Mar-21. If it exceeds it should return FALSE in R33, if not it should return the correct date.
    I tried following formula, but it does not work. Please help.

    =IF(DATE(YEAR(Q33)+P34,MONTH(Q33)+Q34,DAY(Q33)+R34)>(DATE(YEAR(Q33)+(Q33>DATE(YEAR(Q33),3,31)),3,31),"FALSE",(DATE(YEAR(Q33)+P34,MONTH(Q33)+Q34,DAY(Q33)+R34))

    Thanks in anticipation.

    1. Hello Mahesh!
      The formula you wrote does not work. I don’t quite understand why you have the end of the year - March 31. May 4 plus 200 days is November 20, and not November 16, as you have.
      I suggest using the formula

      =IF(DATE(YEAR(Q33)+P34, MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34 > R33,Q33,DATE(YEAR(Q33)+P34,MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34)

      where R33 is the end date of the year.

      1. Hello Alexander!
        This is used for calculating interest on investment starting and ending in same Fin. Year (FY).
        We have FY starting 1 April and ending 31 March e.g., 1-Apr-20 to 31-Mar-21.
        Thus tenure can be max 1 year or less than 12 months or less than 365 days or in months and days.
        Starting date (Q33) is always =TODAY() and cell is locked, but end date (R33) can not be more than 31-March of FY. Thus if tenure input (P34 Y, Q34 M, R34 D) given is such that end date goes beyond 31-March cell (R33) must return "FALSE".
        First part of formula has to add tenure to start date, > (THEN) part of formula has to find FY end date using start date, and (else) part has to either give end date or if more than FY end date return FALSE.
        Tried your suggestion, but it seeks >#REF! as follows: =IF(DATE(YEAR(Q33)+P34, MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34>#REF!,Q33,DATE(YEAR(Q33)+P34,MONTH(EDATE(Q33,Q34)),DAY(Q33))+R34). This is because formula is being built in cell R33 so it cannot have input >R33
        Kindly suggest.
        Thanks.

        1. Hello Mahesh!
          I copied the formula from a blog in Excel and made sure that it works. Perhaps the error #REF! caused by incorrect copying. To make the formula fit your needs, use

          =IF((EDATE(Q33,Q34+P34*12)+R34) > DATE(2021,3,31),FALSE,EDATE(Q33,Q34+P34*12)+R34)

          1. Hello Alexander.
            This formula works.
            But, I would request you to help make year change automatic i.e., there should be no need to change (2021,3,31) with every FY change in >DATE(2021,3,31) e.g. if date next year is 09-May-2021 it should become >DATE(2022,3,31). Please suggest replacement of Date(2021,3,31) with a formula to achieve it.
            Thanks.

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

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

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

  6. Hi,
    I am using a formula like "=IF(D49>=35,"PASS", IF(E49>=35,"PASS", IF(F49>=35,"PASS", IF(G49>=35,"PASS", IF(H49>=35,"PASS", "FAIL")))))"
    In this any one cell 35 also its coming "PASS". Please get me some solution in this and resolve with an appropriate formula.

    1. Hello MURALI!
      I’m sorry but your task is not entirely clear to me. What result do you want to get? Could you please describe it in more detail? Thank you!

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

  8. Hi, i am so terrible with code, a little help with the following would be much appreciated.

    how do i write an if statement for, If Column 'N' has text 'Not completed' then Column 'O' must be a mandatory cell to fill(text required)

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

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

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

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

  13. If you are trying to match two different spreadsheet, one spreadsheet has a number that matches the other spreadsheet but you need to enter the code number in another cell on the other sheet can you write a formula for that ?

  14. Hi- am I able to get a cell to say “1 week” if a different cell says “Extreme”?
    If so can multiple choices and responses then be made to the same cell?

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

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

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

        1. I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find. Give an example of the source data and the expected result.
          It’ll help me understand it better and find a solution for you. Thank you.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 :)