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 124. Total comments: 4830

  1. how can I use If function so that a value is set if true otherwise the user determine the vale
    for example : If( B4="good" , 0, enter a value)

  2. can I use it in fill color?

  3. How do I set up a formula to equal 1 if one cell is the same letter as another or 0 if there is no exact match. For example:
    B1= C, C1=C therefore the value is 1

    or IF B1=C and C1=D, the value is 0

  4. I need a if statement on the below example:

    if employe = Government, Banking , Insurance and >300 it should give 3 and more than >1000 it should give 5

  5. Hi can you help me write a formula for multiple if's. I am trying to make the cell say if G18=2 then print 5.00%, if G18=3 then print 10.00%. If G18=4 then print 15.00%. Going up to if G18=10 then print 45.00%. However when I put in multiple if statements it says TRUE if there is a value in the range or FALSE if not. The formula I put in is, =IF(G18=2,"5")=IF(G18=3,"10")=IF(G18=4,"15")=IF(G18=5,"20")=IF(G18=6,"25")=IF(G18=7,"30")=IF(G18=8,"35")=IF(G18=9,"40")=IF(G18=10,"45").
    Please ac==can you help as I cannot find how to do it.

    1. done it don't worry, I did this instead =IF(G14=2,5,IF(G14=3,10,IF(G14=4,15,IF(G14=5,20,IF(G14=6,25,IF(G14=7,30,IF(G14=8,35,IF(G14=9,40,IF(G14=10,45,)))))))))

  6. Hello,
    I would like to know the formula for this problem.
    409 is in A1 cell.
    i tried with if formula - I am not getting the result.
    out of 409 units - for the first 100 units sold at 2.60, 101-150 units
    sold at 3.15, 151-200 units at 3.55 and 201-409 units sold at 3.85.

    kindly guide me

  7. I have an issue with excel 2010. I have a cell B24 with a formula that produces a number result. I also have cell D13 with a formula that produces a number result as well.

    In a third cell I want the results of B24 and D13 to be compared. Currently, the formula in this third cell is =if(D13=B24,"OK","STOP!!") however it is not working... Please help. :)

    1. Hello Wikelani,

      The formula is absolutely correct and I don't see any reason for it not working. Does it deliver wrong results in your worksheet?

  8. Hi.

    I have spent several hours on this IF formula and it's still really not doing what i want.

    =IF(F3I4;F3*1,45;IF(F3>J4;F3*1,4;IF(F3>K4*1,35;IF(F3>L4*1,3;)))))

    H4=1000 I4=1500 J4=3000 K4=5000 L4=10000

    What i am trying to obtain is that if value in F3 is between 0-1000 the amount in F3 is to be multiplied by 1,50. if the amount is between 1001-1500 it is to be multiplied by 1,45 and if F3 is between 1501-3000 it is to be multiplied by 1,40 and so on

    Seems to be working for the first 2 but then it continues to multiplie all the other with 1,45

    Any help would be very appreciated

  9. Hi Vegil,

    You can embed the COUNTIF function in the logical_test argument, like this:
    =IF(COUNTIF($B$1:$B$6,$A1)>1, "More than 1 values match in Column B", "")

  10. Hello,

    I want to compare column A and B and if the value of column B has more than one match from column A the formula should return an error. For example, I have entered "America" in A1 and if there are more than 1 "America" in B column values it will return "More than 1 values match in Column B". Can you help me give the excel formula for that?

  11. Hello,
    Issue is I need to display certain modified dates dependent on what abbreviation is in block D2 I need to add +30, +60, or +145 to the date in Column M depending on what is in Column D and if D is blank then I need E to be blank also which is where the modified dates will be shown. what I tried is “=IF(D2=AAM,M2+30,””)&IF(D2=ARCOM,M2+60,””)&IF(D2=MSM,M2=145,””)”but that obviously is not working what is the secret formula to make magic happen.

    1. Please use
      =IF(D2="AAM", M2+30, IF(D2="ARCOM",M2+60,IF(D2="MSM",M2+145,"")))

  12. Hallo! First of all, thank you very much for helping so many people!

    I am experienced with Excel, but I am having problem with this: I have two cells (they can contain an error or not). In case of no errors, return the sum of them. If one of them has an error, return the one that hasn't.

    I am doing this below for example, but it´s missing the last part (between " "):

    =WENN(UND(ISTFEHL B1=FALSCH;ISTFEHL B2 =FALSCH);B1+B2;"the one that has no error")

    Yeah, it´s in german. Wenn=if, und=and, istfehl=iferror, falsch=false. At the moment, the error that those two cels can have is "#BEZUG!", but I want the formula to work in case of any errors.

    Thank you beforehand!!

    1. Hi Mia,

      I am not sure I fully understand the required logic, but you may try a formula similar to this:
      =IFERROR(B1+B2; "the one that has no error")

  13. So far, I'm using
    =IF(L3="Regular seating","1","0")
    =IF(L3="Procession/Nominee-Seating","1","0")
    =IF(L3="Other","1","0")

    but I don't know how to tally up the number of each of the replies, which is really what I need to do.
    Can you help? THANKS

  14. Hi I am currently running the formula =NETWORKDAYS(K21,M21,Holidays!A25:A31)-1
    looking to add into that formula IF(K21="N/A","N/A") but I can't seem to get it to sit right with the formula, basically I am looking to make it run the formula if there is a date input into K21 but if N/A is put into K21 I want it to display N/A rather thank the #value error etc? hopefully this makes some sence?

    1. Hi Leigh,

      You can wrap your formula in the IF function in the following way:
      =IF(K21="N/A", "N/A", NETWORKDAYS(K21,M21,Holidays!A25:A31)-1)

      1. Thank you so much, I tried every variation but couldnt for the life of me get it to work! haha @:)

  15. Sorry, my example removed all the blank cells

    1. Hi Jim,

      Try the following formula:
      =IF(AND(A1="x",B1="x",C1="x"),"yes","no")

  16. Hello, I am trying to create a result from three columns who's value is X (the actual text X)
    IF all columns = X "Yes"
    IF any or all columns are blank = "No"

    A B C D
    X X X Yes
    X X No
    X X No
    X X No
    X No
    X No
    X X No
    X No
    No

  17. Hello,

    I am trying to design a new spreadsheet as below

    22/05/2015 05/06/2015
    07/05/2015 07/05/2015 21/05/2015

    Basically I want cell c to add 7 days to cell a if there is not date in cell b. If there is a day in cell b I want it to add 7 days to the date in cell b

    1. Hi John,

      The formula can be as simple as:
      =IF(B1="", A1+7, B1+7)

      Just remember to apply the Date format to column C.

  18. Hi,
    please help me to solve my problem. see formula below

    IF(S64"closed",R$1-S64)

    R1 value July 13 2015
    S64 value July 13 2015
    so the answer is zero but it shows false
    i need zero answer

    1. Hi Kyra,

      You have the same cell reference S64 both in the logical test and value_if_true. It simply cannot be "closed" and "July 13 2015" at the same time :)

  19. Hi,

    I refer to the previous value of the cell using INDIRECT (ROW, COLUMN) formulae but only want to do this IF the date is the same IF date changes I want to keep fixed value of 500
    it would look like this but I am not sure of what formulae to use
    IF( DATE SAME use INDIRECT (ROW COLUMN) IF DATE DIFFERENT FROM PREVIOUS use value 500)
    This is to track the amount of money entering in a day with an amount of 500 reset at the beginning of the day.

    Thank you,

    Aashman

  20. Thanks a lot Svetlana. Using the IF function with ISNUMBER and Search helped me solve a situation in Excel.

  21. Hi,

    A cell has a date entered in it. Now I want to pop-up an alert, or ring an alarm if the date is today. How can I do it.

  22. Hi,

    I am needing a formulae to highlight the cells if the date entered is within a week from the current date. How can I do this?

    1. Hi Iona,

      Do you want to highlight dates that are +- 7 days from the current day, or those that are within the current week?

      1. I would like it to highlight dates that +7 days or less from the current.

          1. That's great thanks. How do I change the words 'TRUE' and 'FALSE'.

            1. The formula returns TRUE and FALSE when you enter it in a cell. If you create a conditional formatting rule with this formula, it will highlight the cells that meet the condition. For the detailed steps to create a rule, please see the link in my previous reply.

              If you want the formula to return some other words, enclose it in the IF function, for example:
              =IF(ABS(TODAY()-A2)<=7, "recent date", "older date")

              1. Works perfectly, thanks.

  23. 7/10/2015 6/10/2015 In this cell i want the Latest one.
    6/10/2015 7/10/2015 In this cell i want the Latest one.

    How could it Possible.

    1. Hi Suvendu,

      Supposing that the dates are in columns A and B, you can use the following MAX formula:
      =MAX(A1:B1)

  24. I am trying to use a IF/Find or IF/Search expression to locate the total expense associated with a particular budget name. I want to search an entire worksheet for a particular name "Total 81320 Communications" which may be in column c or d of the worksheet - and then report the value found in column x from that same row as the name.

    Is this the correct way to build a formula - with the IF(ISNUMBER (SEARCH"text",worksheet A1:V100)

    1. Hi NANCY,

      Yes, it's the correct approach. Here's a real-life formula example:
      =IF(ISNUMBER(SEARCH("deliv",C2)), "No", "Yes")

      You can view the result the formula returns in "Example 3. IF formula for text values with partial match".

  25. Hi Team,

    I have a list of entries. In that only 5 entries are critical and score given is high. If any mistake in any one of the cell overall score should be zero. Can you please help me how it can implemented

  26. Hi,,

    in pivot table, figure show in thousand. I want this amount in Lacs, how to convert this.

    Example : suppose result in pivot table is 500000, but it would be showing in as 5.

    Pl. help

  27. I am faced a big problem. need your urgent help.like below
    BE=1st
    OB=2nd
    US=3rd
    RS=1st
    KR=2nd ...........................
    but i want a formula when i put BE then automatically convert another column 1st or 2nd or 3rd,
    Please help me................................

  28. Hi Svetlana,

    I just want an answer for my work. It happens that our shared worksheet contains some formula like this:

    =IF(PO2>PT2,CONCATENATE(PT2,"-",PO2),CONCATENATE(PO2,"-",PT2))

    cell PO2 contains: Brussels (Greater Area)
    cell PT2 contains: Dublin (Greater Area)

    result: Brussels (Greater Area)-Dublin (Greater Area)

    I'm just wondering how does the formula performs and calculate the "string" ?? It did recognize which is greater in both cells. Coz I am only aware that the ">" function only works with numbers. So hopefully you could help me with this. Thank you so much in advance Svetlana and have a good day ahead.

  29. I need a formula that will do the following:

    Cell A:A = "City-name"
    Cell G:G = total hours > 0.00
    I need to place in Cell H the total hours of rows G:G if A:A = "City-name"

    Keep in mind that I have two ranges here in the equation. A:A is row list of cities that I can choose from. G:G is a row list of hours that I need totaled for each city name in cell H:H

  30. Hi, In Sheet1, I have 2 columns i.e. Company_Name and Credit_Period(No.of Days). In Sheet2 the company name appears in several rows and in the adjacent cell, I want it to pick-up the credit period (as mentioned in Sheet1). Can you guide for a formula please.
    Thanks in advance for your help.

  31. Hi, I would like some help please. I am creating a spreadsheet to do a detailed payroll forecast, as part of this I need to forecast by month by individual for the next 18 months what the monthly provision will be. We start this accrual once the employee has reached five years, and in that first month I need to accrue the first five years and each month after just the monthly amount. Can you recommend a formula that I could use in this case, or a simple method. I have tried a number of options but no luck. Thank you.

  32. Hi, is it possible to extract text from one cell to another cell in another sheet, but only if an "if" function is used or if a condition of a cell is "yes" or "no"?

  33. Hi Svetlana,

    How can i condition +- in excel, i got a value lets say 15 +-0.5 how can i arrange that in a excel formula, if the value is +0.5 fail also if it is -0.5

    Many thanks for any help you could give me

    1. Hi Charles,

      You can use a formula similar to this:
      =IF(OR(A1>15+0.5, A1<15-0.5),"fail", "pass")

  34. Hello Svetlana
    I have bit of unique requirement to automate one of my job responsibilities at work. I have
    Rates – Zone 1, Zone2, Zone3 and so on...
    Each Zone has two rates for consignment depending on its weight, so to get the billing amount of each consignment I have to check what zone and how much it weights and them I can calculate billing amount.
    I am not good at advance excel so I turned to internet for help and landed on your ablebits.com.
    e.g
    Zone 1 - £10 up to 25kg and thereafter 0.35 per/kg
    Consignment of 35Kg = ((35-25)*0.35)+10 = 13.5

    Zone 2 - £8 up to 25kg and thereafter 0.45 per/kg
    Consignment of 35Kg = ((35-25)*0.45)+8 = 12.5

    I was trying to use IF function and was hoping that I can have drop down list from where I just have to choose Zone1 / Zone 2 and it will calculate accordingly. Hope I was clear about my requirement.
    I would be very grateful if you can help, Thanks in advance.

  35. Hello Svetlana
    I have an Excel spreadsheet with 2 sets of data using the same names. The names in the first list are in an order. The second list has the same names but are in a different order. Is there an If function that gives me a number in the second list that tells me what position the name is in the first list i.e. first name in list 1 is third name in list 2 so I need to see 1 next to the third name in list 2 (and vice versa)
    Thanks

  36. I'm trying to do an 'IF' formula where =IF(P7>0,IF(ISBLANK(A256),Type B7 in A256, IF(ISBLANK(A257),Type B7 in A257,IF(ISBLANK(A258),Type B7 in A258, nothing))), nothing)

    I can't seem to find the exact way to type it. If you could help, that would be great. Thanks.

  37. I need a formula where it sum the amount of column B if the date in column A is between the dates in columns C and D.

    1. The date in column A should be greater than the date in column C and less than the date in column D.

      1. Hello Mili,

        You can use an array formula similar to this:

        =SUM(IF((B2:B9>C2:C9) * (B2:B9<D2:D9), A2:A9,""))

        Remember to press Ctrl+Shift+Enter to enter it correctly.

  38. The formula actually did work. The page was not refreshing. The only issue I am having now is that it is returning a "False" if it doesnt meet the criteria. How can I get it not to do that? I want to average my column and it doesnt work with the False.

  39. I have an excel that I am trying to get to display the number that is in a column (AB) in to column (AO)if it has specific words in it. This is the formula that I tried but I am getting a value of 0 which is not correct.

    =IF(H15="2nd Level Support Indiv. Prod. Mahwah", SUM(AB15))

    I am guessing the SUM function is wrong but couldnt think of anythign else to use. Any ideas?

  40. Hi there,

    Wondering if anyone could help me. I need a formula where it searches in a column for a particular word, then it shows a particular cell.

    If(A:A,"WOR",C5)

    Is this possible? Many many thanks,
    Yuko

    1. Hi Yuko,

      You need to write a formula for one cell, and then you can copy it to as many cells as you want or across the entire column:
      =If(A1="WOR",$C$5, "")

  41. Could you tell me what is wrong with this formula
    =IF(R3<=24,24-Q3,R3=0)

    1. The value_if_false argument. You cannot use R3=0 because the formula can return a value only in the cell in which it is entered.

  42. Hi,
    can someone help me answer the below;
    I would like to search, for example, "major" or "minor" in C1, if C1 contains either major or minor, i want to return "True".

    table below;
    C1= major minor young old , return "True"
    C1= major young, return True
    C1= minor old, return True
    C1= young old, return False.

    Much appreciate for your help.

    Regards,

    1. Hi Bravo,

      Here you go:

      =IF(OR(ISNUMBER(SEARCH("minor",C1)), ISNUMBER(SEARCH("major",C1))),TRUE, FALSE)

  43. Hi Svetlana,
    Can You help me to this formula.
    I want to correct this beacuase it always appear ) or #value
    =IF(F13>1,L13=F13),IF(F13<1,ROUND((B10-0.065*2)/F13+1),"")

    Thanks,
    ken

  44. Hi Svetlana,
    I have a query regarding cell value restriction. I want to restrict the use of a certain letter in a cell if a corresponding cell value is greater than a certain amount i.e. if D35 has a value greater than 3, then D32 restricts me from entering the letter D. Is there a formula to do this?
    Regards,
    Paul

  45. Svetlana

    I have a cell with text such as TX,EP. If TX appears it should
    be mutiplied by 5%. I tried the following but it does not seem to
    work

    =IF(AF2739= "TX", AF2739*0.06,)

    Need your help to point out whether this is the correct manner
    of doing it. Thanks

    1. Sorry should be AG2739*.06

      1. H Thong,

        You probably meant "multiplied by 6%". If so, the formula =IF(AF2739="TX", AG2739*0.06,) is correct.

        1. Svetlana

          Thanks

  46. Sir/Madam
    what I want is, if, a1=>1 then b1 will be "sometext"(whatever iwant)
    I used one formula =IF(A3>="","","--rana--").
    but when i am typing in cell a1 is 090, b1 is not responding. Means, before 90 the 0 is not considering.
    Pl. let me know the salvation.
    Thanks

  47. can u give me any formula for allouting different room to different age group

    as example
    age is 30,32,45,56,66,75,65,78
    there are 5 room as j1 j2 j3 j4 j5
    what should be the formula
    please reply fast

  48. Hi,

    I'm using an IF formula which is designed to look at a data tab and find a match for a number in one column and return the corresponding values from another column. This is working well expect one of the numbers, 3370, is also returning the values attached to 333700 and 533700. How do I ensure that the values returned are just for an EXACT match and not numbers which include 3370??

    Hope you can help!

  49. I will say again that for some reason greater than and less than symbols won't show

    1. Hi Hardwareman,

      Our blog engine cuts off greater than and less than symbols quote often, sorry for that. I think you can use a nested IF formula like this:
      =IF(T11<>"","", IF(R11<>"",F11,""))

  50. the first formula should read =if(p11K10,P10-K10,"")

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