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

  1. I want to add today's date to a cell (F9) if cell F12 is populated. If it is blank, I want F9 to stay blank. Not working with what I've tried. Please help. thanks

    =IF(F9=" ",(" "),(DATE(TODAY(),TODAY(),1))) - is not correct. Giving me NUM#

  2. Hi Team,
    If "formula" Can we use with particular cell in name sppose "punit12&₹" This name i have english alphabets,numbers with special character so usse "if" Formula is "count" Other "No" In No candition I am ussing name with hindi character or chainse character other etc character
    Pls do the needful
    Regards
    Punit
    +91 7065751520

  3. Hi
    Please assist with formula: If date cell in sheet 1 is the same date from a column of dates in sheet 2, copy data in the respective row in sheet 2

    Thank you
    Beth

  4. Our appraisal office is looking to create an excel spreadsheet to manage the amount of assignments we can accept. I was hoping to create an IF function for a date only if we type "YES" in the column. I have tried but was not successful.

    Ex. Date Given to us 10/08/19 ---> Inspected? YES ---> Due Date would be the IF function 10/08/19+2 business days, if successful it should automatically input 10/10/19.

    Is this possible?

  5. I want actual value(obtained Data) in a cell but if value become 0 so want to my cell show <10
    so what i do

  6. How i shall formulate if PASS,FAIL by making statements from text such as P/A which indicates as present and absent

  7. Hi,
    I read through the comments but not sure if a similar type of question asked.
    What function is best used if I have a list of items which will be answered Yes/No. I want a cell to state yes if all the cells are ticked as yes, if not all of them is yes (even if only one is no) that cell should say No.
    Currently I am trying the IF function but just struggling to get it working.
    Thanks for your assistance.

  8. Hi
    I need a formula if cell A2 = any cell in column c it will copy text contained cell d2
    Thanks

  9. Can you help me with this formula:
    i have 3 coloums A,B,C
    A. Sl No
    B. Name
    C Code
    in this if i enter name in B Cell is it possible to pic automatically code in C Cell. please help me.

  10. Hello, I am trying to use IF to help auto populate a sheet. I have 12 sheets, what I want to do is put a sheet name in the reference cell and have a cell from that sheet displayed. Example in A1 sheet name. B1 'sheet name'!B3.
    Two thigs i have tryed
    =IF(OR(A1=421,”’421’!BE”,””,OR(A1=424,”’424’!B3,””,OR( A1=429,”’429’!B3”,””))))
    =IF(OR(A1=421,A1=424,A1=429),”’A1’!B3”,””)

  11. I need help with....insert an IF function in cell F5 that shows "PASS" if greater than 59 and "FAIL" if the requirement is not met.

  12. Hi,
    I need your help with the following formula please.
    If A1 is greater than B1, then C1 should show the value typed in A1, and if B1 is greater than A1, C1 should show the value in B1.
    =IF(A1>B1,"A1","B1") or =IF(A1>B1,"A1",IF(A1<B1,"B1")) When I do it, it shows the name of the cell and not the value :(
    Thank you in advance

  13. Wow. This is exactly what I've been looking for. The "Excel if statement for text with partial match" is what I have been trying to do for a day now at work and finally I found this. You made my life so much easier. Thank you!

  14. Hi
    I'm trying to write a formula to read every cell in each row and replace the value if its bigger than 1 with the first value in the row ( each row has a different value in the first cell ) , should i use conditional formatting ? if yes how should i write the formula ?

  15. I need a formula that would copy the text entered in cell C to cel D only when cell A or B is marked with 'x'? Blank entries in cell C would also be skipped is that possible?

  16. Hello,

    I need to search two separate text in one column to return "Yes" of "No". What am I doing wrong I this formula?
    IF(AND(ISNUMBER(SEARCH("welcome",F2)("Good",F2)),F3>10),"Yes","No")

  17. WHAT FORMULA CAN I use to look for the VALUE in cell "A2" is equal to CELL VALUE IN "I14" in a different sheet - but I want it to do a search in multiple sheets (75 to be exact) then SEARCH IN MULTIPLE SHEETS and if there is a match to "A2" return back WITH THE VALUE IN CELL "L34" of the sheet were the value was found.

  18. I m using if conditions below
    =If(A1<=1000,"0",IF(A2001,"80")))

    BUT WHEN I USE SUN FUNCTION TO GRAND TOTAL, AMOUNT SHOWING ONLY '0'

    KINDLY ADVISE AND HELP PLEASE

  19. WHAT FORMULA CAN I use to look for the VALUE in cell 'A2" is equal to CELL VALUE IN "I14" in a different sheet - but I want it to do a search in multiple sheets (75 to be exact) then SEARCH IN MULTIPLE SHEETS and if there is a match to "A2" return back WITH THE VALUE IN CELL L34 of the sheet were the value was found.

  20. hi please cou;d you tell me where I am going wrong:
    I need a formula for the following:
    column O2 contains a due date, column P2 contains completed status of Yes or No. I need to add a status of overdue or Due within 90 days.
    so if the date in column O2 is over 90 days from today then the status is "Overdue" but if the date in column O2 is within 90 days from today then the status needs to be "Due within 90 days"

  21. I need a formula to calculate a value from a group of numbers in a column:
    Column A - has dollar figures
    Column B - has dates WHEN the amount in column A is paid.
    I want to calculate the unpaid amounts from column A when column B doesn't have a date entered or a blank cell.

  22. hello iam sandip mere ko aisa formate make karna he exm:-90 he to 75 ke above 10 ana chahiye 81 ke above 15 86 ke above 20 91 ke above 25 ager 75 ke below rahega to 0 ana chiye mene ye formate banaya he mager ye kam nahi kar rha he,
    =IF(C4>=76,"10",IF(C4>=81,"15",IF(C4>=86,"20",IF(C4>=91,"25",IF(C4<=75,"0")))))
    please help me my whatsapp no.8669800963

  23. I am trying to write a formula to distinguish between if a serial # starts with a Letter or a Number then multiply by two different % rates . Any help would be great.

  24. I am using an IFS function to write a color ("Red", "Yellow", or "Green") in cell F4. It is dependent upon the results (a number between 1 & 5) in another cell (AA4). If I type a number into the cell AA4, the formula yields the appropriate color. However, when I use a formula (Average) to generate the number, it does not work. I get an error message (#N/A in Excel and #Name? in Sheets). How do I get the IFS function to recognize the results in the cell instead of the formula?

    1. Problem solved. It was a rounding issue. Even though I could only see a 1, 2, 3, 4, or 5, the underlying numbers were 1.??, 2.??, etc., so the IF statements were not true. I used the Round function in the Average formula and solved the problem.

  25. How to enter some number in another cell according to ented number in first cell. ie. If 1 is enterd in column 1st then atomatically 300 (rate per kg) is enterd in money column. Please answer quickly. Thankyou for readin and answering me.

  26. Hi All
    Can you please help me with a formula? I need to create a monthly report.
    Reporting period is from 26th previous month to 25th of the current month for example for May 2019 reporting commence from 26th April to 25th May.
    Can you help? Many thanks in advance.
    Jonathan

  27. Hi There,
    Please help me to come up with the formula i needed.
    In a column, if there is a value encoded the result must be 8 or 0 only.
    How is it?

    Thanks a lot.

  28. Hello,
    I'm trying to condition a cell (A) color based on 4 other cells (B,C,D,E).
    The first one is a case number and the 4 cells are attempts to reach a customer via phone.
    So, if a first call is made and the customer is not reached, B cell will contain text, A cell will change to yellow.
    When a second call attempt will be made, C cell will contain text and A cell will need to change to orange.
    After the third call attempt is made, D cell will contain text and A cell will need to change to red.
    If the customer is reached at any given point (1st,2nd,3rd attempt), E cell will change to value (Y) and A cell will need to change to green.
    Can you help with this?
    Thank you

  29. Hello Team
    How to find the TP value with IF condition by using TYPE.
    TYPE ENTRY PRICE TP
    Sell 1.34365 1.34165
    Buy 0.69792 0.69992
    Please help me with this.

    Thanks

  30. I am trying to write an equation that will do the following:
    If the value for B6*0.25 is less than 1,000, I want to display 1,000, and then if the value of B6*0.25 is greater than 1,000, I want it to print that value. Below is my failed attempt. Thank you for your help.
    =IF(B6*0.251000,B6*0.25))

    1. =IF(K133*0.25<1000,"1000",0)

  31. Hello I'm trying to write an IF statement that results in showing two conditions when met. For example, I input -5 in the reference cell I want the IF statement to punch out 5 below or if the number is positive to punch out 5 above in the same cell.

    1. Use this;
      =IF($R14<0,($R14*-1)&" Below",($R14*1)&" Above")

  32. Hi I am trying to use the if function but I am stumped.
    I need me S/Sheet to be blank is 0, if between 1 and 28 show yes and higher than 28 No.
    My current formula is =IF((AND(F2>=1, F2<=28)), "Yes", "No") but the everything with a 0 gets a no when I need it blank
    HELP PLEASE xx

    1. Use this;
      =IF($F11=0,"",IF($F11<=28,"Yes","NO"))

  33. In excel there was 8 sheets, on that 1 sheet is the master sheet
    If we enter any email id in column A in any of the 8 sheets it has to auto reflect into Master sheet on another below.
    Ex. in sheet 2 I have entered abc@gmail.com
    in sheet 3 I have entered bcd@gmail.com
    in sheet 7 I have entered jkq@gmail.com
    result should be entered mail auto reflect into master sheet one another below

    Please help in this regard

  34. I am trying to do travel rates based on where an employee visits. I would like to create a formula that includes 50 states and top cities within those states.
    So if John travels to Washington State he choose WA from a drop down box. From there in the next cell cities will become available such as Seattle, Tacoma and Spokane. Based on which city John chooses travel rates will appear in the next cells such as acceptable hotel rates, breakfast, lunch and dinner. If John chooses AZ then Flagstaff, Phoenix, Sedona and Tucson appear...makes sense? Possible?

    Hope anyone can help

    1. Yes it's possible with conditional drop down list.
      Need data work on it.

  35. COLUMN A COLUMN B COLUMN C RESULT

    OTE3‐L1‐IM‐300992‐01 12-Apr COMPLETE
    OTE3‐L1‐IM‐300992‐01 13-Apr COMPLETE
    OTE3‐L1‐IM‐300992‐02 13-Apr INCOMPLETE
    OTE3‐L1‐IM‐300992‐02 INCOMPLETE
    OTE3‐L1‐IM‐300992‐03 13-Apr INCOMPLETE
    OTE3‐L1‐IM‐300992‐03 INCOMPLETE
    OTE3‐L1‐IM‐300992‐03 INCOMPLETE
    OTE3‐L1‐IM‐300992‐03 INCOMPLETE
    I need a result of complete or incomplete when look at the

  36. StockName CurrentQty InvestmentPrice CurrentMarketPrice HoldingValue MarketValue
    BGRENERGY 14 483.00 58.25 6762.00 815.50
    BRIGADE 29 390.00 229.25 11310.00 6648.25
    BSE 18 806.00 610.45 14508.00 11132.10
    COALINDIA 100 232.75 250.50 23275.00 25050.00
    DIXON 8 1766.00 2353.95 14128.00 18831.60
    DLF 5 932.52 174.30 4662.60 871.50
    EDL 69 371.70 6.80 25647.30 469.20
    ENDURANCE 30 472.00 1156.20 14160.00 34686.00
    ERIS 24 603.00 632.75 14472.00 15186.00
    GICRE 32 433.50 244.60 13872.00 7827.20
    Please sum col E & F How to a cetain text numbers

  37. In column A (there are 4 sentence choices: I want an Apple, I don't like Bananas, I love coconuts, I need water)

    A1 I want an Apple
    A2 I don't like Bananas
    A3 I love coconuts
    A4 I need water

    In column B, I want to use a function to check column A and search for partial text, then past a value in cell. For example, in B1, checks for 4 possible conditions in A1, check for "ppl" and copy the value "Yes" to cell in B1 OR check for "Ban" and copy the value "No" to cell B1 OR check for "coco" and copy the value to B1 OR check for "wat" and copy the value "end" to B1.

    What function should I use?

    1. If (AND condition works here..

  38. In column A (there are 4 sentence choices: I want an Apple, I don't like Bananas, I love coconuts, I need water)

    A1 I want an Apple
    A2 I don't like Bananas
    A3 I love coconuts
    A4 I need water

    In column B, I want to use a function to check column A and search for partial text, then past a value in cell. For example, in B1, check A1 for "ppl" and copy the value "Yes" to cell in B1

    What function should I use?

  39. How can I formulate a cell to cover a range of one column and when the number in that column reaches zero or below zero, the cell will populate with a column next to the original range with that 2nd column's date.
    Example column A shows balance, and when loan balance reaches zero (or below) populate the date in the column next to show when loan is paid off.

  40. I need an example, which shows if cell contains the specified the value it should return as it is, if not it should run the following formula.

  41. HOw to write the following using IF ?

    0.70-0.75 1
    0.75-0.80 0.9
    0.80-0.85 0.8
    0.85-0.90 0.7
    0.90-0.95 0.6
    0.95-1.00 0.5
    1.00-1.05 0.4
    1.05-1.10 0.3
    1.10-1.15 0.2
    1.15-1.20 0.1
    1.20-1.25 0

  42. Hello;
    I have the formula which i stated below, doesn't work. Could you help me on this matter.
    The idea is: i will insert the data in C30 and if it's blank it will show "Please insert rank in cell C30" but if it's not blank it will show the value that searched through the table which is between "b33 and b41". So far it worked (without using AND formula).
    Lastly i want the insert values to be between 0 and 10. So i put the and formula but it doesnt work.
    Sincerely.
    =AND(IF(ISBLANK($C$30);"Please Insert Rank in cell C30";(INDEX($B$33:$B$41;$C$30)));(c30>0;c30<10)

  43. Hi, im looking for the right formula to identify the value statement.
    example:
    Category Description Amount BDO Eastwest
    Cash In BDO 500 (formula?)- on how to appear the amount

  44. if i want to input a value in cell A1, then each time the output value in cell B1 must be added to that value. in this scenario which formula i should use ?

  45. Hi, I'm trying to create a formula that will provide a "True" statement if the cell is blank between 1/1/19 and 5/30/19. Then a formula to identify blank cells after 6/30/19. I'm using these formulas for conditional formatting.

  46. I want to see automatic date in cell if I enter some dispatch quantity
    Example--
    Date Dispatch Quantity
    Today Date 150 and any other number >0

  47. is there any formula to find out the total figer of a person whose name in a sheet 3 to 4 time.

  48. AMT Clientname client code
    42560 SAP 1234
    31760 DELL 4321
    2150 INTEL 3412

  49. i want to user if and in single formula
    exp:A=>1&B=SAP=1234
    A=>1&B=DELL=4321
    A=>1&B=INTEL=3412

  50. I want to know how to work this formula out: in cell M1, I add F1+ I1+J1+K1+L1, but if the total in cell F1 an I1 is 0 then the total should be 0

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