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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hi A, Perfecto! Works like a dream. Thank you very much.

  32. Hi Carrie,
    please try the formula below.
    =IF(COUNTIF(SoldDetails!C8:C21,"NO"),"INCOMPLETE","COMPLETE")

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

  34. Thank you works like a charm.

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

  36. If *100 is not there it works for stocks, with the * 100 it works for options.

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

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

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

  40. 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), Re-submit (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 re-submit 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.

  41. Hy I Make A Inventory Sheet And I Want To Highlight That Row Where Quantity is 0.

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

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

  44. Hi Steven,
    =SUM(G6:G100)+SUM(F6:F90)-SUM(E6:E90)
    I'm not sure if that is what you mean.

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

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

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

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

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

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

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