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

  1. having a hard time getting this to work.
    voice and screen true - yes, false = no

  2. I have door numbers listed in boxes across. I need a formula that totals the number of boxes filled in with any number. 201, 202, 203 = 3

    1. =@COUNT( DID IT.

  3. i want to know haw to get value to close value by if function

  4. Hi
    I am trying to create if statement that has both text and number here is the code:

    =IF(U18="YES",VLOOKUP(W18,'Assumptions and Factors (2)'!U148:U150,1,VLOOKUP(Q18,'Assumptions and Factors (2)'!W110:X135,2),VLOOKUP(Q18,'Assumptions and Factors (2)'!Z110:AA135,2)))

    it is to create manhours on specific pipe sizes (Q18=3.00,'ADDS IN A MODIFIER,but if W18 is not None,1B,ET1, it goes to another table modifier)

    I do get #value when I do a calculation step it shows me but I just cannot seem to get pass this.

    any direction would be helpful
    Thank you

  5. I am attempting to return a set of responses should a series of the following exist...

    If Material/Service/Other COlumn = "Service", MSA column = "N" , COI Column = "N" , & COI Valid/Expired = "Expired"......= "No COI and MSA"

    and then in the same cell

    if(and(Material/Service/Other Column="Service", MSA Column="N", COI Column="Y",& COI Valid/Expired="Expired"...="Expired COI and No MSA"

    and continuing with the same cell and adding on to the above...

    If(and(material/service/other column="Service", MSA Column="Y", COI Column="N",& COI Valid/Expired="Expired"....="No COI"

  6. Due Date Date of Deposite Delayed days Penalty Amt
    10/21/2015 10/26/2015 5 2000

    Interest for Late Filing of Return
    Particular Amount
    Up to 30 day 2,000.00
    Above 30 day 5,000.00

    how to put IF formula in penalty amt column if file the return before 30 days (penalty Rs.2000) after 30 days (penalty Rs.5000) and on the same day (no late file the return)

  7. Hi,

    I need a =if(or) formula

    =IF(OR(G153="Sta",451)=IF(G153="Fra",939)=IF(G153="LOG",294)

  8. Hi, I have to collect samples of employees dust exposure at a underground mine.
    Here are the conditions of re-sampling a sample dependent on the lab results:
    Underground
    • Below 0.3 mg/m3 (Re-sample one sample)
    • Above 5 mg/m3 (Re-sample one sample)
    • Above 10 mg/m3 (Re-sample two samples)

    Can you please help me with a formula?

  9. Hi I want a formula which is suitable for my data entry sheet Ex: if any party name or amount entered into sheet1 would change the amount in sheet2 simultaneously with the same name and amount.

  10. I am writing a spreadsheet where column d has a validation so only 6 options are available from a drop down menu. I would like to have the next cell auto populate with a specific value based on the previous choice what would my formula look like.

    ex: row 1column d reads refrigerator column e should populate 0001
    row 2 column d reads stove column e should populate 0002

  11. Thanks for your response Ahmed. However your formula did not work.
    Here is a portion of my worksheet.
    Date Achieved
    (mm/yy) CARRY (5 Points)
    Feb-2015 1.Carry an object held against body with 2
    hands.
    Apr-2016 2.Carry an object held against body with 1
    hand.
    Apr-2016 3.Carry an object away from body with 2 hands.
    4.Carry an object away from body with 1 hand.
    May-2017 5.Carry two (2) objects – one in each hand.

    As you can see in this sample, the skill of Carry has 5 parts. In Feb. 2015 the student achieved 1 part in the skill of Carry. In April 2016, the student achieved 3 parts and in 2017 the student achieved 4 parts of the skill.
    Now in my chart. What happens when I use the formula =COUNT(A1:A5)EXCEL adds all the parts achieved which is what I want it to do BUT NOT change the number in the previous year. So my chart should look like this:
    (the dates are linked to years in another worksheets so they come in when a new year is added)
    Feb-2015 Apr-2016 May-2017
    1 3 4
    Hope someone can help with this.
    Thanks, Debra

  12. Hi
    I want to translate the following equation
    IF($D$35>0;D35-C35;0)

  13. I do hope you can help. I have tried numerous IF, COUNT,COUNTIF,COUNTA formulas to no avail.
    I am trying to track the progress of the number of skills achieved each year over the course of multiple years. Each skill is broken into multiple parts. When a part of the skill is achieved a date (such as Mar -2015) is recorded in the cell next to that part of the skill.
    I wanted to create a chart to show the date and the number of skills that was achieved for each year over the course of multiple years.
    So I created a chart with 2 rows with 10 columns under the skill. The first row records the dates, the second row records the number of items achieved in that year. I want EXCEL to automatically record this information

    I need Excel to count the number of skills the student achieved for the year and put it in the cell under the date. Now each year I give the test I want it to record how many skills the student achieves without Excel changing the number of skills achieved from the previous year.

  14. Hi,

    Can you please help me to get the data using "if" function..

    Data....
    Batch Joining Date

    Batch 1 10/10/2015
    Batch 2 10/17/2015
    Batch 3 10/24/2015
    Batch 4 11/7/2015
    Batch 5 11/14/2015
    Batch 6 11/21/2015
    Batch 7 12/5/2015
    Batch 8 12/12/2015
    Batch 9 12/19/2015
    Batch 101/2/2016

    Type of training

    Induction training 2 weeks IT
    Technical Training 3 weeks TT
    Process training 6 weeks PT

    I need to know, how many members would attend the training from which batch for which training

    can I get this data please

  15. Hi,
    I have trouble with formula, i want make formula will show the specific value if found specific text/string in row, i want to make simple formula for java bytecode you can see the byte code here https://en.wikipedia.org/wiki/Java_bytecode_instruction_listings

    so if i type Mnemonic in first coloum and i get the OpCode (hexdec) as a result in second coloum

  16. I AM HAVING TROUBLE I HAVE A FLOW CHART WITH DUE DATES ON THEM EXAMPLE I HAVE WORK THAT NEEDS TO BE COMPLETED WITH IN 3,6 AND 9 MONTHS HOWEVER IF THEY GET COMPLETED BEFORE I WANT TO NEXT SET OF DATES TO GET TRIGGERED BY COMPLETED TIME IF LESS THEN THE ACTUAL DUE DATE HOW WOULD I DO THIS?

  17. Hi

    If i have to pay someone who has secured the below marks

    Equal to 70% - 89% - 10 USD
    90% and above - 20 USD
    Below 69% - 0 USD

    What formula do you use ?

  18. have a formula i have been trying to fix -

    =IF(AND(X16>99.9999%, Y16>99.9999%),(IF(TRUE,W33),IF(AND(X16<99.9999%, Y16<99.9999%),(IF(FALSE,W32)))))

    what im wanting is the X16 + Y16 if both are true to show the info in cell W33 BUT if ether is False to show info in cell W32 - is there something im missing?

  19. Hi I am trying to use check boxes to add up a list of items one might purchase and give me a sum total. I tried the formula for just one box but it keeps giving me zero instead of the dollar amount I expecting ($5).

    The formula I am using is =IF(D3="true",C3, 0)
    In this case D3 is true
    C3 is $5
    The result comes back as 0

    Any ideas?

    1. Hi Paul,

      Because TRUE and FALSE are Boolean values, you don't need to enclose them in quotes, and your formula will work perfectly:
      =IF(D3=TRUE,C3, 0)

      1. Svetlana that works perfectly, now for part two which I thought I could figure out if part one worked. I have a list of prices that if they are true I would like them to total up at the bottom. I tried =IF(D3:D14=TRUE,SUMC3:C14, 0) but it doesn't work

        1. Paul,

          For this, you'd better use the SUMIF function:
          =SUMIF(D3:D14, TRUE, C3:C14)

          The above formula sums values in column C if a cell in the corresponding row in column D is TRUE.

          1. Thank you again, that worked.

  20. Hi I've got a problem on a question with IF Functions. The problem I'm stuck on is saying to enter a formula in cell H6 that determines total cost based on the number of days the vehicle was rented and the discount indicated in B42(Discount). If the payment was Express Miles or Rewards(B42) then the customer should receive the discount.

    Days rented is= E6
    Daily rate is= G6
    This is what I have so far.

    =IF(F6="Express Miles",G6*E6(1-Discount),G6*E6)

    Thanks for helping if thats enough info!

  21. Hello,

    Just a quick one.

    I need some assistance with this...

    =IF(F6="A", H6)

    For Example

    F G H
    6 A =IF(F6="A", H6) Hello
    7 Goodbye

    I need the if command to also pick up other values too

  22. Could someone please help me describe how writing an incorrect IF statement might produce the wrong data??

  23. Hi!

    Good day! I was trying to link the formula from one cell to another.
    sample: =IF(D14=0,"Required Field","") ((This is under E14)
    Formula cannot be used id created under D14.

    Or are there any other formula that can produce the same answer which is "Required Field"

    Your help is much appreciated.

    Thank you.

    1. Hi Jenah,

      >Formula cannot be used id created under D14.

      Absolutely right. You cannot enter a formula in a cell that contains other data. If you enter it in E14, the formula should work fine.

      Please note that D14=0 checks D14 for 0. If you want to check for an empty cell, use D14="".

  24. hiiiii
    i want to place a specific word in all the row.but some row is having a specific word

    for eg. 1 name having dr.sameer
    2 name having kanti

    so i want dr. in all name which is having word dr in it or not

  25. Hi!

    Good day! I am currently working on an Application Form. I don't want them to missed any required fields. Is there any formula that we can use to prevent them in missing any fields or "this is a required field" will prompt on each missed out cell.

    =IF(D6=" "," ","Required Field")is not working. Help please!

    Thank you!

    1. Hi Jenah,

      If you want the formula to return "Required Field" when D6 is empty, you should put it the other way round:

      =IF(D6="","Required Field", "")

  26. Hi there,
    I want formula for this
    if in cell A1 I write "MISC CON_Sep15_vat12"
    in cell B2 i have to write VAT AMOUNT.IN other words
    if THE cell A1 contains THE WORD VAT THEN only I can enter any numerical value in THE CELL B2

  27. Please help to apply:

    If A1>48, “5 Years” A1>36, “4 Years” A1>24, “3 Years”

  28. Hi Svetlana:
    My formula is: =IF(D$2>=$B$1,D3-D4-D5,0)
    D2 is "Q1'17"
    B1 is "Q3'16"
    The result based on above formula is "0", which I know it should be the calculation based on D3-D4-D5. Would you please help correcting the formula so it can recognize that Q1'17 is greater than Q3'16.
    Thank you

  29. Hi Diana,

    In all Excel formulas, you need to enclose text strings, not numbers, in double quotes, like "x".

    So, here's the formula as per your conditions:

    =IF(A12="X"; 0; IF(ISNUMBER(A12); 1; ""))

    If A12 is neither "x" nor number, the formula will return an empty string (blank cell).

  30. Hi Svetlana. Please heeeelp me with a formula. So, if in my table, I have X, I want the result to be "0"and if in the table appears a number, the result to be "1". I've tried:
    IF(A12=X,"0";"1") but it doesn't work. It gives me NAME as result

  31. Hi Svetlana,

    I am looking for a formula that will look across a range of values to see if any of the values contain a specific value (note: the values will never be exact) and then present the match in another range of cells.

    For example: in column A I have a list of songs:
    A1: free bird
    A2: you really got me
    A3: the pretender
    A4: the bird is the word

    I'd like to look across the range A1:A4 and then display the name of each song ONLY if it contains the word "bird" in it. So in column B for example: B1: free bird and B2: the bird is the word would display.

    1. Hi Joe,

      Here you are:
      =IF(ISNUMBER(SEARCH("bird",A1)), A1, "")

  32. I need the formula if A2=50, 51, 52, 53, 54, 55, 56, 57, 58, 59 then result will be 1, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9 respectively.

  33. Hi Svetlana!
    Please help me with my formula. This is how it goes: If A1 to A5 is greater than or equal to 3 and less than or equal to 10, show "OK" but if not, show "NG". This is the formula I have come up with but it doesn't seem to work: =IF(AND(A1:A5>=3,A1:A5<=10),"OK","NG")

    Thank you in advance and I'm looking forward for your reply.
    -Paul

    1. Hi Paul,

      The IF function does not work correctly with ranges, you need to reference each cell individually, like this:

      =IF(AND(AND(A1>=3,A1:A5<=10), AND(A2>=3,A2<=10), AND(A3>=3,A3<=10), AND(A4>=3,A4<=10), AND(A5>=3,A5<=10)), "OK","NG")

      Alternatively, you can use a more compact array formula (remember to press Ctrl+Shift+Enter to enter it correctly):

      =IF(SUM((A1:A5>=3) * (A1:A5<=10))=5, "ok", "ng")

  34. Hi There,

    Want to multiply cell B & C if cell A is greater than 0. Anything less than 0 in cell A should return 0.

    Thanks,
    Agana

    1. Hi Agana,

      Here you go:
      =IF(A1>0, B1*C1, 0)

  35. have a formula i have been trying to fix -

    =IF(AND(X16>99.9999%, Y16>99.9999%),(IF(TRUE,W33),IF(AND(X16<99.9999%, Y16<99.9999%),(IF(FALSE,W32)))))

    what im wanting is the X16 + Y16 if both are true to show the info in cell W33 BUT if ether is False to show info in cell W32

  36. Hi there,

    In collum 'A', I have different categories. Collum 'B' and 'C' are both used for names, and collum 'D' is used for the value of each thing.

    However, I need a total for each category.
    I tried stuff like "=IF (Tabel1[Soort])=amp , =SOM(Tabel1[Prijs]) , ", but it doen't seem to work. Anyone who knows if Excel is capable of this?

    All tips are offcourse apreciated.

    Laurens

  37. Hi Svetlana,

    cold you please help me with below.

    There are two rates for the same product. If i buy 10 units, my cost is 2 per unit. If i buy more than 10 units, then cost will be 2 per first 10 units and 3 per additional unit. I need to buy 16. What should be the formula.

    thanks in advance

  38. =IF(AND(E15:E222,"INS",E15:E222,"HTS"),"7"," 14")

    HI,
    I NEED A HELP WITH FORMULA. WITHIN A CELL,THE CONDITION IS, GET THE NUMBER 7 IF ALL THE CELLS WITHIN THE COLUMN APPEARS "INS" ON FILTERATION AND 14 IF ALL THE CELLS CONTAINS "HTS".

    THANKS IN ADVANCE.

    INS
    HTS
    HTS
    HTS
    HTS
    HTS
    HTS
    HTS
    INS
    HTS
    HTS
    HTS
    INS
    HTS
    HTS
    INS
    HTS
    HTS
    HTS

  39. Hi,
    I am using a pivot table which provides dates the future event is to occur. I need to display (in a cell outside of the pivot table) the date furthest in the future. I am using the formula
    ="First date "&text(max(date),"mm dd yyyy")
    But, I keep getting the response of First date 01 00 1900.
    What am I doing wrong?

    Thanks in advance,
    Alicia

  40. HI! I need help with a formula. In a cell, the condition of the formula is either a plain number or percentage...how do I make the condition for that if the condition is a plain number will show or a percentage? I used the If nested function but when adding the percentage, it keeps saying "error"
    What should I do?

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

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

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

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

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

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

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

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

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

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

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