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 88. Total comments: 4833

  1. I want to know about one formula, the details are as below;

    col A - Location
    Col b - Salary
    col c - ratings
    col d - ?

    in column d, I want a formula that some selected location will fall under A group and the percentage of increment given will be 7 and the rest will fall under B group and the percentage of increment given will be 5. Can you think of a formula for the above.

  2. Hello I am trying to create a worksheet with employees names in column B (which I choose daily from a validation list) when I click on the names I want their internal rate to populate in column J and external rate to populate in column J automatically-I have these names & rates on a separate sheet and right now am using the drop down list to do it manually-there must be a better way... Can this be done?

    1. Sorry I meant internal rate in column G and externalrate in column J

  3. Hi, can someone help me with an IF formula as follows:

    If Cell C17 contains "PASS", and Cell C30 contains "PASS", then PASS should be returned, if not, FAIL should be returned.

    1. Hi May,

      What you need is an If formula with nested AND like this:
      =IF(AND(C17="PASS", C30="PASS"), "PASS", "FAIL")

  4. How can get a formula to calculate the mark up on an item based on the category code. We have 3000 items and 40 categories. We have a master list on a separate worksheet for the categories with their markup. I want to markup the cost based on the category. For example:
    Worksheet #1 Worksheet #2
    Category Cost Sell Price Category Markup
    SNA 10.00 BOT 1.5
    CAP 15.00 SNA 1.8
    CAP 1.6

    I can use an IF formula, but I'm hoping there is something quicker as opposed to typing a 40 item nested IF formula. Thanks.

  5. hi , I am trying to write a nested formula. the formula I am using is
    =if(c4="jose","good",or(c4="jesus","better",""))
    The problem that I am running into is, that it reads the first nested formula but it will not read the second. can you help me with this formula. or recommend another

    1. Hi Jesse,

      You don't need OR in nested IF's:

      =IF(C4="jose", "good", IF(C4="jesus","better",""))

      1. thanks

  6. Hi,

    I am using an IF statement to check if 2 cells match.

    However where 1 cell is blank (not yet populated) it will reflect as "same"

    Can I use isblank in combination to bring back a blank cell if both referenced cells aren't populated.

    =IF(AC118"",(AC118=P118,"SAME","CHANGE"), "")

    1. Hi!

      Try this formula:

      =IF(AND(AC118="", P118=""), "", IF(AC118=P118,"SAME","CHANGE"))

  7. Hi,

    Could somebody help me with this issue:

    I'd would like a formula where this condition is met.

    A1 B1 C1 D1
    A2 B2 C2 D2
    A3 B3 C3 D3
    A4 B4 C4 D4

    The formula for all cells in column A: if the cell below is empty return to the first cell in column D. So for example if cell A5 is empty I want the value of D1. But also A6 is empty so i want the value of D2. Can sb help me with this?

    Thanks you in advance

    Nicolas

  8. i'M SORRY I meant using the today statement...

  9. I need to create a if formula to populate an statement for this:

    5/29/16 10:25 PM

    I need to collect any data that if before today at 3:00 pm

    any help is welcome,

    Thanks !

  10. =IF(N8:N19="Completed","Yes","No")

    i have tried this to test list of cells which are updated thru a data validation list its not working please anyone can help.

    1. The list of Data validation referes like this

      Completed , in progress , on hold , new

      these are the four validation can be selected now i want this above formula to work to tell me once are completed.

      =IF(N8:N19="Completed","Yes","No")

      i have tried this to test list of cells which are updated thru a data validation list its not working please anyone can help.

  11. Formula below wont halve the figure as it should
    result when c33 is 2 is 100%. When c33 is 0 the result is only changing to 95%
    =IF(C33="0", R16/2, R16)

  12. hi i need a follow for below.

    = 90% 1%
    >= 100% 1.30%
    >= 110% 1.50%
    >= 115% 1.75%

    thank you

  13. Hi.
    I need a formula
    Example
    Alpha123
    After a formula

    Cell-1.Alpha
    Cells -2..123

  14. Hi I'm setting up a stores system, I have set figures in the min & max and a stock level that can be adjusted by a user.
    Min is in row G13, Max is in row I13, Stock level is in row K13.

    I need help to put a formula in L13 to say "min stock" or "stock ok" or "no stock"
    So when the stock level in K13 is the same as the min level in G13 it says "min stock", if stock level in K13 is greater than min level in G13 it says "stock ok", if the stock level in K13 is zero it says "no stock".
    Thank You

  15. I need formula, if cell a1 is (3), cell b1 should be insert text "good"

    1. hi Zvonko,

      =if(A1=3,"good","")

  16. I have a unit quantity in cells B5-J5.
    I have prices in cells B6-J6.
    When someone enters YES in cells B8-J8, then cell K8 should total units booked, but not allow units to exceed a quantity of 10.
    Cell L8 should total row 6 dollar value of cells with a yes placed in corresponding row (in this case row 8) from values in B6-J6.

    Thank You!

  17. sorry got working with

    =IF(ISBLANK(AE5), TODAY()-M5,AM5-M5)

    now just the colouring..?

    1. Select the column(s) you want to color excluding header rows, and create a conditional formatting rule with the below formula (which checks if a cell in column AE is blank):
      =$AE5=""

      The detailed steps to create a formula-based rule can be found here.

  18. also if using todays date due to null can this appear in red so know not complete?

  19. Hello, I want AM5 to =AE5-M5 to count days between to dates. If AE5 null I want it to count to todays date. I can't get my logic to fit logic!!

  20. Hi,

    Please help me for getting the formula for below

    If B1 have some date mentioned then A1 has to come delivered
    if B1 dont have date it sould come in transit

    1. Hello Bahubali,

      Try this one:

      =IF(B1="", "in transit", "delivered")

  21. hi,

    i have a issue in excel for using a formula. my problem is sum of range in a column.for example, a table contains data describe below:-
    sr.no. class student name marks
    1 sixth parmod 166
    2 sixth kamal 140
    3 sixth parmod 250
    4 seventh kamal 270
    5 seventh parmod 180
    i want the sum of parmod marks of six th class. how and which formula is used for it......

    please help
    i have to find parmod marks of class sixth

    1. i used it but something wrong.
      =if(b:b="sixth",if(c:c="parmod",sumif(c:c,"parmod",d:d)))

      1. Hello Parmod,

        To conditionally sum cells, use SUMIF or SUMIFS function to sum with one or several criteria, respectively.

        In your case, you can use this formula:
        =SUMIFS(D:D,C:C, "parmod",B:B, "sixth")

        Where column D contains marks, C - names, and B - classes.

  22. Hi,
    ordered qty,Produced qty ,bal to produce qty, ready for delivery qty, and delivered qty, delivery date .How can I follow up before one week of deadline ?
    And could you please provide me a gantt chart to solve this ?

  23. In a purchasing department, there will always be a lot of suppliers to handle and a lot of POs to make. I have created a drop down box of the list of suppliers, and what formula can i use that if i selected a supplier name, the address and the vedor code will appear on a different cell.

    sample is if i choose Supplier A in A11, address will appear in A12 and vendor code will appear in M7

    hope you will be able to help me.

    Thanks!

    JUBI

  24. Hi. I need to write a formula in one cell to say "if we have market data (cell L10), use the market data. If not, check to see whether the average company rates fall between the preferred and floor rates.

    Thank you!

  25. HI,

    ACTUALLY I REQUIRED FORMULA FOR KNOWING PRESENT STATUS OF ACTIVITY. WE UPDATES ACTIVITIES DATE WISE IN A SHEET AND OTHER SHEET WE UPDATE ALL BRIDGES NO. NOW I AM LOOKING FOR THAT MY LAST ACTIVITIES OF BRIDGES IS AUTOMATICALLY UPDATE IN OTHER SHEET.

    REGARDS,

    KUNDAN

  26. Hi. Iam trying to calculate a formula which calculates using 2 different prices.
    One column is old price, and second is new price.
    I need excel to calculate using the only new price in second column which is originally empty, whenever a value present. Can anyone help?

    Thanks
    Dandy

  27. Thanks Svetlana, for the reply

  28. Hi,

    I have a situation where i want to use the IF formula. Column A has dates. column B has Carpet area of a flat. Column C has additions of areas such as addition of all areas above. Now, what i want in Column D is if the date is 31-March-16 or less than that, the column D should not add the areas and if it is mote than 31-Mar-16, the it should add the column above. Example is as below:---

    A B C D
    31-Mar-16 1960 1960 1960
    31-Mar-16 1960 3920 1960
    31-Mar-16 1960 5880 1960
    01-Apr-16 1960 7840 3920
    05-Apr-16 1960 9800 5880

  29. Hello, I am trying to reference a cell in another sheet that will change based on the date. Sheet 1 has 3 cells that I use to count the number of tasks completed in a day. Sheet 2 is a tally for each day, so for the 5/20 row on sheet 2, I would want the values from the 3 cells on sheet 1 to flow to that row when the today's date is 5/20. On 5/23, I would want the same cells from sheet 1 to flow to the 5/23 row on sheet 2. I tried the following function: =IF(TODAY=A15,Sheet1!B2,0) where A15 is the date (5/20/16 in today's case) but I get the #NAME? error. Thanks.

  30. Hi Svetlana

    Hi have a formula that I'm struggling with. Can you please assist
    =IF(D9:Q9>0,(D4:Q4="PH")+(D9:Q9))
    Basically I want my Public Holiday column to add the hours in Row 9 only if Row 4 has the value PH

    WAGES
    F/Night: 26/3/2016 - 08/04/16
    Hours + Days WORK PAYMENTS
    PH PH
    SHIFT PAY NAME 26 27 28 29 30 31 1 2 3 4 5 6 7 8 PER Nor Over Sun P/H Sick Fam Res Absent Leave
    # # SA SO MA DI WO TH FR SA SO MA DI WO TH FR F/NIGHT 1x 1.5x 1.5x 2x 1x 1x 0 1x
    TEAM A
    1 229 Oscar Matemane Day Day Night Day Day Day Off off Off Day Day Day Day Absent
    Hours 10 10 9 11 11 11 0 0 0 11 11 11 11 FALSE 106 #VALUE! 10 #VALUE!

  31. I have a data validation list column (in cell O2) with the following pull down options: Elimination, Substitution, Engineering, Administration, Culture, PPE. If Elimination is selected, I would like the adjacent cell to return the value 0.1. If Substitution is selected, the adjacent cell should have the value 0.3. Other values for remaining text are 0.5, 0.75, 0.8, and 0.9.

    If you can figure this out you will be my forever hero!

    1. Here i have one question..Dont know its possible with excel or not..If any one have idea than tell me..
      I have data validation list in cell D5 with the drop down options: Residential , Commercial..I have another drop down list in E5 with options Flat , Bunglow , Shop , Office ...If i select Residential in D5 then in cell E5 it shows only 2 option Flat , Bunglow..Dont show me another options like Shop , Office..

  32. Hello!

    I'm trying to make a formula based off a date column.
    Ideally, I would like to have a separate column populate a 'blank' or "Follow UP" based on whether or not the date is older than 15 days.

    I tried =IF(C1-TODAY()>15,"FOLLOW UP","") but obviously it did not work.

    Am I on the right track?

    1. Hello Claire,

      You were almost there! :)

      =IF(TODAY()-C1>15,"FOLLOW UP","")

  33. please help me how to make the formula... this is the situation!

    Name of Studet PG MG SFG FG CG Remarks
    1 Homer Lee 1.5 2.5 3 1.9 2.2 PASSED
    2 Howard Chan 3.2 3.5 3.4 3.8 3.5 FAILED
    3 Mark Tee 1.6 1.8 1.4 2.2 1.8 PASSED
    4 John Bree DROPPED DROPPED DROPPED DROPPED DROPPED FAILED

    WHAT SHOULD i do in my consolidated Grade (CG) that will appeared droppped not FAILED TO JOHN BREE

  34. hi,

    if A2 is less than (negative) or equals to zero then it should come the same value as in A2,

    But if A2 is greater than zero then it should result A2 * 10% ( or any percent which i want to put)

    reply

    1. Hi Naresh,

      Here you go:

      =IF(A2<=0, A2, A2*10%)

  35. I need a formula whereby if the value of cell A2 is 199 or less, then "Low," if the value of cell A2 is from 200-399, then "Medium," or if the value of cell A2 is 400 or above, then "High."

    Would appreciate any help!

    1. Hello!

      Try this formula:

      =IF(A2<=199, "Low", IF(A2<=399, "Medium", "High"))

  36. if A1 is 372 the total must reflect in C1

  37. I used the following formula to print the value of the cell that met the conditions but what should I add to the formula not to show a blank instead check the next cell if it has value and print it accordingly without leaving a blank space:

  38. Hi,
    Please help. This formula I need:

    Evry name from column A have value in column B (for example, name in cell A1 have value in cell B1, name in cell A2 have value in cell B2 etc.)
    Some of that names from column A I have in column D, and I need enter matching values in column E for evry name from column B.
    So I need some formula like this:
    IF value from d4 is located in column A enter here matching value from column B.

    Thank you.

  39. looking for a formula for the following:

    IF F5 is < or=100, then 4
    if F5 is 125 then 5
    if F5 is 150 then 6
    if F5 is 175 then 7
    if F5 is 200 then 8
    if F5 is 225 then 9
    if F5 is 250 then 10

    Thank you!

  40. s.no. name credit period Overdue Total overdue 30 45 60 90 120
    1 adam 30 ? 824 5 45 51 658 65
    2 rab 45 ? 1338 6 2 25 654 651
    3 bab 60 ? 1041 45 4 469 159 364
    4 carv 90 ? 1044 5 42 748 124 125
    5 careg 120 ? 973 45 42 49 512 325

    819

  41. Can any one give the formula to given table.

    s.no. name credit period Overdue Total overdue 30 45 60 90 120
    1 adam 30 ? 824 5 45 51 658 65
    2 rab 45 ? 1338 6 2 25 654 651
    3 bab 60 ? 1041 45 4 469 159 364
    4 carv 90 ? 1044 5 42 748 124 125
    5 careg 120 ? 973 45 42 49 512 325

    i want to get the sum after sum after the credit period amount with the forumula.

  42. hi

    i want to create a formula using the if function to calculate compensation. i have 5 staff with each a different joining dates but same leave date.

    can anyone please guide me?

  43. I want to set a formula for my KRI, if my depoist is 3m kri should score 15 ( 100 %), if 2M kri should score (2/3) 66% of 15 i.e 10. The bench mark is 3 which is 100% and KRI score is 15 (100 %) and if the deposit fall below KRI should move with the same percent, how Am i suppose to do this , please help.

  44. Hi

    I want that if me or someone else fill in, for example in cell A2 the number 160161 it should be in cell A3 the time 05:30

    Also if i or someone else fill in a number that not exist from my numbers then a text for example "wrong number"

    Can i get help with a formula for that?

    IF=160131 in cell A2 then 05:30 in cell A3.
    And IF not none of the number then text wrong number

    Thanks in advance

    1. Hi Tommy,

      If you want to see value "05:30" when A2 contains number 160161, you can enter the following formula into A3:
      =IF(A2=160161,"05:30","wrong number")

      If you want to make sure the cell is formatted as time, please use the following formula instead:
      =IF(A2=160161,TIMEVALUE("05:30"),"wrong number")

      I hope this helps

  45. When I use SUM to add up a column of numbers returned by an IF formula, it does not work. Why is that?

  46. I want to use SUM to add up the column of numbers resulting from an IF formula, but it returns 0. What should I do please?

  47. hi
    i'm trying to use IF in condition more than 13
    below is formula i put in excel, it wouldn't work

    =IF(AP3=1,"mkra",IF(AP3=2,"kumÖ³",IF(AP3=3,"mina",IF(AP3=4,"emsa",IF(AP3=5,"]sPa",IF(AP3=6,"mifuna",IF(AP3=7,"kkáda",IF(AP3=8,"sIha",""))))))))&IF(AP3=9,"",IF(AP3=10,"",IF(AP3=11,"","")))

    Could you please help me?

    Best regard,
    Sreymom

  48. I'm trying to determine the monthly salary of our instructors. They get 30$/hr if they teach less than 120 hours/month, but receive 35$ for each additional hour after the 120 hr threshold.

    This is the formula I put in, but it wouldn't work:

    =IF(C2>120, (120*D2)+(C2-120)*35;C2*D2)

    Could you please help me out?

    thanks,
    melih

    1. Hello Melih,

      Your formula is correct except for a typo and one little detail. Depending on the locale you have in Excel, please make sure you use either commas or semicolons as separators, i.e. either
      =IF(C2>120,(120*D2)+(C2-120)*35,C2*D2)
      or
      =IF(C2>120;(120*D2)+(C2-120)*35;C2*D2)

      Assuming you always have the regular rate per hour in cell D2, you need to make it an absolute reference so that it doesn't change to D3, D4, etc. in other rows:
      =IF(C2>120,(120*$D$2)+(C2-120)*35,C2*$D$2)

  49. GD,

    I am working on stock control excel table. I need the IF formula to give me the updated price for the updated stock quantity if I receive an item with new price. How can I do this please?

    BR.

  50. Can you please help me to make a formula for the dates of delays

    sample

    (A)Date Submitted (B)Date Return (C)No. of days taken
    14-Dec-15 17-Dec-15 3
    14-Dec-15 I need a value of this one for everyday counting till the return date to show it is overdue and continue to all spreedsheet.

    hope you can help me

    Regards,

    Jm

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