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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. HI

    I have a problem where I have to write an if function for "if the last digit in a number is even enter 1000 if not, 0". I can't figure out how to put the "last digit is even" part into the formula to make it work in excel.

    Thanks in advance

    1. Hi Peter,

      You can use the RIGHT function to get the last digit and ISEVEN to find out whether it's even. For example:
      =IF(ISEVEN(RIGHT(A1,1)), 1000, 0)

  32. Hi,

    I want to do a if function to a data range (not just one cell). For example, for data ranga A1 to Z1, I key in =if(A1:Z1=2,"1","0"), but of course this is incorrect. Can you help me get the right formula or function for this one?

    your help is much appreciated.

    Thanks.

  33. Hi,
    I have several columns with dates (in ddmmmyy format). I want to determine which date in a row is the most recent and determine how recent it is by comparing it to today's date. How do I do that?
    Thanks in advance,
    Henry

  34. (6516513) <<>> (6516681321)

    this two codes are different but meaning is same. Is this possible? ( if I could teach to excel that this codes are same and if it is possible to create formula which will find (using vlookup or other formula) one of this code give him some colour and same time this formula gonna find second code which is same and gave second ones same colour)??? in case of every other code colour must be different :(

  35. Hi,

    can you please help me to pick the start and end date of following excel.

    WO CO start End 1-Sep 2-Sep 3-Sep 4-Sep 5-Sep 6-Sep 7-Sep 8-Sep 9-Sep 10-Sep
    11856 MK1501 30
    11857 MK1502 60 60 60 60 30
    11858 MK1503 30 60 60 60 60

  36. =IF(F9:F10<0,'1A'!P3:P3+'1A'!F9:F10,MASTER!P3+1)
    Please help me for adding two column cell in formula

    Thanks

  37. I have cells c6:t6(achieved scores of students) but the obtainable score is on c4:t4. that is, if in cell c6 a student scores 0 but the obtainable score on C4 is 25, meaning they score 0 of 25, D6=3 and D4=3 (meaning 3 out of 3). I want U6 to be the sum of obtainable scores.

  38. Hi,

    i have a lot of machines need annual certification, and i neeed to apply if function to let me know
    if the date of certification "EXPIRED" or "VALID" or will be expired within one month

    Thanks
    Abbas

  39. HI,

    So i have A1 - D1 as breakdown values for total in E1 and;
    have 'Paid' in F1 and the date in G1 and;
    have the same amounts as A1 - D1 in H1 - K1 if its paid.
    what i want is to automatically have the amounts in A1 - D1 in H1 - K1 if i type in Paid in F1.

    Can you tell me a IF formula i can use for this?

  40. Actually I want any formula To I Used in excel To sort out data Like We have 3 Column SIZE DIA and Buyer Code
    Buyer Code is F-015 This buyer want Many Sizes different Sizes different dia 41.5 dia is 150X200 29 Dia is 100X200 and 47 Dia is 180X200 So i want any condition i put Buyer code and Dia auto detect its size

  41. hi friends i want a something mentioned below

    if G5 between 90 and 95 then Pass other wise Fail

    Please help

    Thanks

  42. Can your "value_if_true" or "value_if_false" statements in an IF function reference pull text from another cell?

  43. Need for for calculating if age falls within a certain range then to return a set figure. I have generated this but its giving me no joy:

    =IF(AND(B6>=18,B6=31,B6=51,B6<=64),3.5)

  44. Hi,

    I have 2 columns with dates in them.
    A=Current Due Date
    B=Reschedule To date

    How can I do a logical test to show in a 3rd column all the reschedule dates are more than a week apart from the current due date?

    Thanks,

  45. Hello,
    I want to create a formula , for to look in coloumn B2:B100, in cell text contain is "Raj" then print "Raj availabel" in A1 cell

  46. hi,

    I have data tabke

    date time open high low close

    and I want to convert the data for a day/days into time ranges so that I can chck how it moved with each day as a unique day.as the O H L C will chane with in the day only as er time change requested

    If I wat to pull the 1st open for a time range, how do I pull the 1st open even though it is not the start time In my report sheet.

    it may so happen that the data may start coming only 2-3 seconds later though say at 9:00:03 where as the start time in my query would by 9:00:00

  47. HI,

    HOW WOULD I WRITE THE FORMULA TO GET THE FOLLOWING

    IF a0,IF(A3="INDIA",6,IF(A3="NEPAL",10,IF(A3="SRILANKA",15,))))),"")

    LEADTIME
    INDIA = 8 DAYS
    NEPAL = 10 DAYS
    SRILANKA = 15 DAYS

  48. Hi,

    How would I write the formula to get the following:

    If A10.99999,A1=0.99999 otherwise = A1.
    I want to Winsorise it to be between 0.00001 and 0.99999

    Thank you for your help!

    Nazi

  49. I want to create a formula. I have 5 types of value like 4.17, 4.33, 4.50, 4.67, and 4.83 I want if 4.17 comes in a cell the dependent cell will show 4 min 10 sec. For 4.33 it will show 4 min 20 sec, 4.50 show 4 min 30 sec, 4.67 show 4 min 40 sec, 4.83 show 4 min 50 sec and 4.00 show 4 min. How can I do it in excel?

    1. the value before decimal is min like 6.17 will represent 6min 10 sec

  50. I have an excel question. I've got a scenario where I'm trying to write and if/or function. Here's what I have so far: =IF(OR(C43=0,C44=0),0,IF(OR(C43120,C44>4),0.15,0.1))). If C43 is between 1-60 or C44 =1, then the discount is 5%. If C43 is between 61-120 or C44 is between 2-4, then the discount is 10%. If C43 is greater than 120 or C44 is greater than 4, then the discount is 15%. Can you help me write this formula? Thank you!

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