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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. I have a list of names in column F and numbers in columns K and P. In column R I have formula =if(p11k12,p12-k12,""). Then in column Q I have, so far, =if(r11,f11,""). This shows the name in F11 in Q11 if there is a value in R11 and it works.
    I need to extend this formula so that if there is a value in column R then show the name in column F but if there is a value in column T then show nothing (leave blank).
    Help please

    Kind regards Tony

  32. Hi,
    I want to make a cell turn blue if I enter a date or red if I enter text.
    I cant figure this out....searched everywhere for info.
    Thanks,
    Jack

    1. Hi JACK,

      You can create the conditional formatting rules, with the following formulas:

      Blue: =ISNUMBER($A2)

      Red: =ISTEXT($A2)

      Where A1 is the top-most cell with data in the column, not including the header.

  33. I have a name in column A. Depending on what it is, column B should have another name. For example. A says "Alaska" B would say "West" or if A said "New York" B would say "Eastern", etc. Can't make it work. Thanks for your help.

    1. Hi Paula,

      What you need is a nested IF formula like this:

      =IF(A1="Alaska", "West", IF(A1="New York", "Eastern", ""))

  34. Hi there.

    I'm trying to create an IF statement that will return nothing if there is nothing in the cell adjacent, but if there is anything text or number in that cell, then it will perform a formula.

    Example

    A1 Says, "Hello" then B1 will perform (B1*B6)

    I can get an IF statement to result in nothing if A1 is blank, but I can't figure the right way to have it do a formula if there is something in A1.

    1. Hi George,

      Simply put the calculation in the value_if_false argument. For example,

      =IF(A1="", "", B1*B6)

  35. I would like to return a list of numbers separated by commas and & signs in an if statement, such as

    =IF((B14<8.75), 5, IF((B14<11.25), 5 & 7.5, IF((B14<13.75), 5, 7.5, & 10, IF((B14<17.5), 5, 7.5, & 12.5, IF((B14<22.5), 7.5, 10, & 15, IF((B14<27.5), 10, 15, & 20, B30))))))

  36. Can IF function for dates be used in a library system?
    I want that the book I've borrowed is in its due date, the date would turn to red or there will be notification that it's already due.

  37. Hi Svetlana, I need help with a formula. I need to compare E1(total sale) to F1(goal)and display 10% of E1(total sale) if the store made or surpassed its F1(goal) and 0 if it did not make its goal.

    1. Hi Sharee,

      Here you go:
      =IF(E1>=F1, E1*0.1, 0)

  38. hi Svetlana, thnx for your help but i need it again. please guide me to make this farmula.
    A1="UA" i want cell A1 to be color in red
    how it will work?
    like if i put the word UA in A1 i want A1 to be filled with Red color otherwise if i put anything else it stays normal

  39. Try this @Adeel
    =if(G1=100,G1=200,F1*30)))

  40. @Adeel
    put this formula in H1
    =IF(G1=100,G1=200,F1*30)))

  41. @Adeel wait 2 min bro miss Svetlana already given me this formula.. let me share it with you

  42. hi Svetlana, thnx for your help but i need it again. please guide me to make this farmula.
    A1="UA" i want cell A1 to be color in red
    how it will work?

  43. I want to link a document so that if it is greater than today it =A4 and if it is less than today it =A3. Say the document with A3 and A4 is called pizza. What would this formula look like?

  44. I want to know the formula as per following data:

    F G H
    1 50 100 (I want if G1 value is less than 100 so F1 multiply with 10 but if G1 value is greater than 100 and less than 200 so F1 multiply with 20 and if G1 value is greater than 200 so F1 multiply with 30 and answer came in H1)

    kindly provide assistance

    1. Hi Adeel,

      Here's the formula for H1:

      =IF(G1<100, F1*10, IF(G1<200, F1*20, F1*30))

  45. Hi Svetlana Cheusheva,
    am just looking to put one formuls to calculate the tax value using IF function.the things are like follows....

    Suppose if there three columns f j and h
    if i enter the value in f as 5.5% then it should give me the value of the f*h in the column j

    ex: (in column j) =IFf=5.5% then it should calculate the value of f*h.
    could you plese help me out in this...

    1. Hi KIRAN,

      If you need a formula exactly for 5.5%, then enter the following one in cell J2:
      =IF(F2=5.5%, F2*H2, "")

      The above formula multiplies F2 by H2 if F2=5.5%, and returns an empty string otherwise.

      If you are looking for a more universal formula that multiplies a value in column H by whatever percentage you enter in column F in the same row, then use this one:

      =IF(F2<>"", F2*H2, "")

  46. Hi Svetland,

    I am using this formula to find out whether 2 values exists in a column, if it doesn't, I want the result to indidate "NO", if it does, then "YES". However, it shows us "FALSE" for those that does exist. Can you check what I did wrong?

    Thank you!

    =IF(ISERROR(VLOOKUP("AP1902"&TRIM($J51)&TRIM($Q51),$C:$C,1,0)),IF(ISERROR(VLOOKUP("A80201"&TRIM($J51)&TRIM($Q51),C:C,1,0)),"NO","YES"))

  47. Hi Svetland,
    Please help me write this IF formula correctly. i have a cell with different figures but some are missing data sets.
    i want a command that says IF F3>1 then the answer should be 1, IF F3<1 then it should be 0 but if F3 is empty (meaning there is no number there) it should be empty. meaning if F3 is empty, leave blank.
    Kindly assist.
    thank you

    1. Hi NASCO,

      You can use a nested IF formula like this:

      =IF(F3>1, 1, IF(F3<1, 0, ""))

      Please pay attention that the formula will also return an empty string if F3=1. So, you may probably want to add one more condition to the formula or replace F3>1 with F3>=1.

  48. Hi

    I am a total excel self taught newbie working on an Excel Workbook recording expenditure and wondered if there was an easy way to input the following in formula format:

    If a number is 50 to 100 minus 5% if a number is greater than 100 minus 10% in excel

    EG if the number in cell K20 is between 50.00 & 100.00 then 5% is deducted and if the number is 100.00 or more then 10% is deduced, I have managed to create a formula to deduct 5% if the number is 50.00 using the IF function =IF(K21>$M2,N2,)
    K21 = total
    M2 = 50.00
    N2 = % sum

    The above works fine however it is picking all amounts above 50.00 as I am unable to set the parameters so it only picks up amounts above 50.00 but less than 100.00

    Any suggestions?

    Many thanks

  49. What if I want more than one logical test in my formula?

    eg: If I want "IN TRANSIT" and "DISPATCHED" to be covered by one formula only.

    Awaiting Response

    1. Hi Aditya,

      You can do this by using nested IF functions, for example:

      =IF(A1="IN TRANSIT", value_if_true, IF(A1="DISPATCHED", value_if_true, [value_if_false]))

      1. Thank you so much. you were a great help.

  50. Hi,

    Thanks for above test eg.it lot me help to learn IF formula.

    Thanks.

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