Using IF function in Excel: formulas for numbers, text, dates, blank cells

The IF function is one of the most popular and useful functions in Excel. You use an IF statement to ask Excel to test a condition and to return one value if the condition is met, and another value if the condition is not met.

In this tutorial, we are going to learn the syntax and common usages of Excel IF function, and then will have a closer look at formula examples that will hopefully prove helpful both to beginners and experienced Excel users.

Excel IF function - syntax and usage

The IF function is one of Excel's logical functions that evaluates a certain condition and returns the value you specify if the condition is TRUE, and another value if the condition is FALSE.

The syntax for Excel IF is as follows:

IF(logical_test, [value_if_true], [value_if_false])

As you see, the IF function has 3 arguments, but only the first one is obligatory, the other two are optional.

  • logical_test - a value or logical expression that can be either TRUE or FALSE. Required.In this argument, you can specify a text value, date, number, or any comparison operator.

    For example, your logical test can be expressed as or B1="sold", B1<12/1/2014, B1=10 or B1>10.

  • value_if_true - the value to return when the logical test evaluates to TRUE, i.e. if the condition is met. Optional.

    For example, the following formula will return the text "Good" if a value in cell B1 is greater than 10: =IF(B1>10, "Good")

  • value_if_false - the value to be returned if the logical test evaluates to FALSE, i.e. if the condition is not met. Optional.

    For example, if you add "Bad" as the third parameter to the above formula, it will return the text "Good" if a value in cell B1 is greater than 10, otherwise, it will return "Bad": =IF(B1>10, "Good", "Bad")

Excel IF function - formula example

Excel IF function - things to remember!

Though the last two parameters of the IF function are optional, your formula may produce unexpected results if you don't know the underlying logic beneath the hood.

  1. If value_if_true is omitted

    If the value_if_true argument is omitted in your Excel IF formula (i.e. there is only a comma following logical_test), the IF function returns zero (0) when the condition is met. Here is an example of such a formula: =IF(B1>10,, "Bad")

    In case you don't want your Excel IF statement to display any value when the condition is met, enter double quotes ("") in the second parameter, like this: =IF(B1>10, "", "Bad"). Technically, in this case the formula returns an empty string, which is invisible to the user but perceivable to other Excel functions.

    The following screenshot demonstrates the above approaches in action, and the second one seems to be more sensible:
    IF formulas with the value_if_true argument omitted

  2. If value_if_false is omitted

    If you don't care what happens when the specified condition is not met, you can omit the 3rd parameter in your Excel IF formulas, which will result in the following.

    If the logical test evaluates to FALSE and the value_if_false parameter is omitted (there is just a closing bracket after the value_if_true argument), the IF function returns the logical value FALSE. It's a bit unexpected, isn't it? Here is an example of such a formula: =IF(B1>10, "Good")

    Putting a comma after the value_if_true argument forces your IF statement to return 0, which doesn't make much sense either: =IF(B1>10, "Good",)

    And again, the most reasonable approach is to put "" in the third argument, in this case you will have empty cells when the condition is not met: =IF(B1>10, "Good", "")
    IF formulas with the value_if_false argument omitted

  3. Get the IF function to display logical values TRUE or FALSE

    For your Excel IF formula to display the logical values TRUE and FALSE when the specified condition is met and not met, respectively, type TRUE in the value_if_true argument. The value_if_false parameter can be FALSE or omitted. Here's a formula example:

    =IF(B1>10, TRUE, FALSE)
    =IF(B1>10, TRUE)
    an example of the IF function that displays logical values TRUE or FALSE

    Note. For your Excel IF statement to return TRUE and FALSE as the logical values (Boolean values) that other Excel formulas can recognize, make sure you don't enclose them in double quotes. A visual indication of a Boolean is middle align in a cell, as you see in the screenshot above.

    If you want "TRUE" and "FALSE" to be usual text values, enclose them in "double quotes". In this case, the returned values will be aligned left and formatted as General. No Excel formula will recognize such "TRUE" and "FALSE" text as logical values.

  4. IF statement to perform a math operation and return a result

    Instead of returning certain values, you can get your IF formula to test the specified condition, perform a corresponding math operation and return a value based on the result. You do this by using arithmetic operators or other Excel functions in the value_if_true and /or value_if_false arguments. Here are just a couple of formula examples:

    Example 1: =IF(A1>B1, C3*10, C3*5)

    The formula compares the values in cells A1 and B1, and if A1 is greater than B1, it multiplies the value in cell C3 by 10, by 5 otherwise.

    Example 2: =IF(A1<>B1, SUM(A1:D1), "")

    The formula compares the values in cells A1 and B1, and if A1 is not equal to B1, the formula returns the sum of values in cells A1:D1, an empty string otherwise.

Using the IF function in Excel - formula examples

Now that you are familiar with the Excel IF function's syntax, let's look at some formula examples and learn how to use IF as a worksheet function in Excel.

Excel IF statement for numbers: greater than, less than, equal to

The use of the IF function with numeric values is based on using different comparison operators to express your conditions. You will find the full list of logical operators illustrated with formula examples in the table below.

Condition Operator Formula Example Description
Greater than > =IF(A2>5, "OK",) If the number in cell A2 is greater than 5, the formula returns "OK"; otherwise 0 is returned.
Less than < =IF(A2<5, "OK", "") If the number in cell A2 is less than 5, the formula returns "OK"; an empty string otherwise.
Equal to = =IF(A2=5, "OK", "Wrong number") If the number in cell A2 is equal to 5, the formula returns "OK"; otherwise the function displays "Wrong number".
Not equal to <> =IF(A2<>5, "Wrong number", "OK") If the number in cell A2 is not equal to 5, the formula returns "Wrong number "; otherwise - "OK".
Greater than or equal to >= =IF(A2>=5, "OK", "Poor") If the number in cell A2 is greater than or equal to 5, the formula returns "OK"; otherwise - "Poor".
Less than or equal to <= =IF(A2<=5, "OK", "") If the number in cell A2 is less than or equal to 5, the formula returns "OK"; an empty string otherwise.

The screenshot below demonstrates the IF formula with the "Greater than or equal to" logical operator in action:
the IF formula with the

Excel IF function examples for text values

Generally, you write an Excel if statement with text using either "equal to" or "not equal to" operator, as demonstrated in a couple of IF examples that follow.

Example 1. Case-insensitive IF formula for text values

Like the overwhelming majority of Excel functions, IF is case-insensitive by default. What it means for you is that logical tests for text values do not recognize case in usual IF formulas.

For example, the following IF formula returns either "Yes" or "No" based on the "Delivery Status" (column C):

=IF(C2="delivered", "No", "Yes")

Translated into the plain English, the formula tells Excel to return "No" if a cell in column C contains the word "Delivered", otherwise return "Yes". At that, it does not really matter how you type the word "Delivered" in the logical_test argument - "delivered", "Delivered", or "DELIVERED". Nor does it matter whether the word "Delivered" is in lowercase or uppercase in the source table, as illustrated in the screenshot below.
Case-insensitive IF formula for text values

Another way to achieve exactly the same result is to use the "not equal to" operator and swap the value_if_true and value_if_false arguments:

=IF(C2<>"delivered", "Yes", "No")

Example 2. Case-sensitive IF formula for text values

If you want a case-sensitive logical test, use the IF function in combination with EXACT that compares two text strings and returns TRUE if the strings are exactly the same, otherwise it returns FALSE. The EXACT functions is case-sensitive, though it ignores formatting differences.

You use IF with EXACT in this way:

=IF(EXACT(C2,"DELIVERED"), "No", "Yes")

Where C is the column to which your logical test applies and "DELIVERED" is the case-sensitive text value that needs to be matched exactly.
Case-sensitive IF formula for text values

Naturally, you can also use a cell reference rather than a text value in the 2nd argument of the EXACT function, if you want to.

Note. When using text values as parameters for your IF formulas, remember to always enclose them in "double quotes".

Example 3. Excel if statement for text with partial match

If you want to base your condition on a partial match rather than exact match, an immediate solution that comes to mind is using wildcard characters (* or ?) in the logical_test argument. However, this simple and obvious approach won't work. Many Excel functions accept wildcards, but regrettably IF is not one of them.

A solution is to use IF in combination with ISNUMBER and SEARCH (case-insensitive) or FIND (case-sensitive) functions.

For example, if No action is required both for "Delivered" and "Out for delivery" items, the following formula will work a treat:

=IF(ISNUMBER(SEARCH("deliv",C2)), "No", "Yes")
IF formula for text values with partial match

We've used the SEARCH function in the above formula since a case-insensitive match suits better for our data. If you want a case-sensitive match, simply replace SEARCH with FIND in this way:

=IF(ISNUMBER(FIND("text", where to search)), value_if_true, value_if_false)

Excel IF formula examples for dates

At first sight, it may seem that IF formulas for dates are identical to IF statements for numeric and text values that we've just discussed. Regrettably, it is not so.

Unlike many other Excel functions, IF cannot recognize dates and interprets them as mere text strings, which is why you cannot express your logical test simply as >"11/19/2014" or >11/19/2014. Neither of the above arguments is correct, alas.

Example 1. IF formulas for dates with DATEVALUE function

To make the Excel IF function recognize a date in your logical test as a date, you have to wrap it in the DATEVALUE function, like this DATEVALUE("11/19/2014"). The complete IF formula may take the following shape:

=IF(C2<DATEVALUE("11/19/2014"), "Completed", "Coming soon")

As illustrated in the screenshot below, this IF formula evaluates the dates in column C and returns "Completed" if a game was played before Nov-11. Otherwise, the formula returns "Coming soon".
An example of the IF formula with the DATEVALUE function

Example 2. IF formulas with TODAY() function

In case you base your condition on the current date, you can use the TODAY() function in the logical_test argument of your IF formula. For example:

=IF(C2<DATEVALUE("11/19/2014"), "Completed", "Coming soon")

Naturally, the Excel IF function can understand more complex logical tests, as demonstrated in the next example.

Example 3. Advanced IF formulas for future and past dates

Suppose, you want to mark only the dates that occur in more than 30 days from now. In this case, you can express the logical_test argument as A2-TODAY()>30. The complete IF formula may be as follows:

=IF(A2-TODAY()>30, "Future date", "")

To point out past dates that occurred more than 30 days ago, you can use the following IF formula:

=IF(TODAY()-A2>30, "Past date", "")
Advanced IF formulas for future and past dates

If you want to have both indications in one column, you will need to use a nested IF function like this:

=IF(A2-TODAY()>30, "Future date", IF(TODAY()-A2>30, "Past date", ""))
A nested IF formula for dates

Excel IF examples for blank, non-blank cells

If you want to somehow mark your data based on a certain cell(s) being empty or not empty, you can either:

  • Use the Excel IF function in conjunction with ISBLANK, or
  • Use the logical expressions ="" (equal to blank) or <>"" (not equal to blank).

The table below explains the difference between these two approaches and provides formula example.

Logical test Description Formula Example
Blank cells ="" Evaluates to TRUE if a specified cell is visually empty, including cells with zero length strings.

Otherwise, evaluates to FALSE.

=IF(A1="", 0, 1)

Returns 0 if A1 is visually blank. Otherwise returns 1.

If A1 contains an empty string, the formula returns 0.

ISBLANK() Evaluates to TRUE is a specified cell contains absolutely nothing - no formula, no empty string returned by some other formula.

Otherwise, evaluates to FALSE.

=IF(ISBLANK(A1), 0, 1)

Returns the results identical to the above formula but treats cells with zero length strings as non-blank cells.

That is, if A1 contains an empty string, the formula returns 1.

Non-blank cells <>"" Evaluates to TRUE if a specified cell contains some data. Otherwise, evaluates to FALSE.

Cells with zero length strings are considered blank.

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

Returns 1 if A1 is non-blank; otherwise returns 0.

If A1 contains an empty string, the formula returns 0.

ISBLANK()=FALSE Evaluates to TRUE if a specified cell is not empty. Otherwise, evaluates to FALSE.

Cells with zero length strings are considered non-blank.


Works the same as the above formula, but returns 1 if A1 contains an empty string.

The following example demonstrates blank / non-blank logical test in action.

Suppose, you have a date in column C only if a corresponding game (column B) was played. Then, you can use either of the following IF formulas to mark completed games:

=IF($C2<>"", "Completed", "")

=IF(ISBLANK($C2)=FALSE, "Completed", "")

Since there are no zero-length strings in our table, both formulas will return identical results:
The IF formula for blank / non-blank cells

Hopefully, the above examples have helped you understand the general logic of the IF function. In practice, however, you would often want a single IF formula to check multiple conditions, and our next article will show you how to tackle this task. In addition, we will also explore nested IF functions, array IF formulas, IFEFFOR and IFNA functions and more. Please stay tuned and thank you for reading!

You may also be interested in:

2,994 Responses to "Using IF function in Excel: formulas for numbers, text, dates, blank cells"

  1. Sajumon says:

    Pls help me create a formula:
    If column B contains a particular value, then find the corresponding value in column A

  2. Doug says:

    Where the value is in A55, the formula to accomplish what you want is:
    =IF(A55>=5,500,"Something Else")
    The formula says, If A55 is greater than or equal to 5, then enter 500 in this cell, otherwise enter Something Else.
    You can change the cell address to the address you need. Also the "Something Else" text to different text. Just remember, if you want the cell to display text to enter it inside quotes. Numbers don't need quotes.

  3. greg says:

    I have trouble creating nested ifs. I have three conditions:
    T1 = 00:00 - 06:59
    T2 = 07:00 - 14:59
    T3 = 15:00 - 23:59
    And these are the cells that I need to match the above conditions. The dates and times are together in one cell.
    Col A
    Row 1: 06/04/2018 06:00:25
    Row 2: 06/04/2018 08:00:26
    Row 3: 06/04/2018 18:35:18

    This is my formula:
    =If(And(A1R1>T1, A1R1T1, A1R2T1, A1R3<T1), T3, "")

    How can I combine this?

    • Doug says:

      The formula you've provided isn't clear. Can you provide your needs in an IF/Then format? For example, IF A1>=6:00 then T1, IF A1>= 7:00 then T2. Something along those lines.

  4. Alex Nkonya says:

    What is the error in this if function?

    • Doug says:

      It looks as though you've got the quotes around the wrong
      This should work:=IF(X4="A",1,IF(X4="B",2,IF(X4>="C",3,IF(X4="D",4,IF(X4="F",5,)))))

  5. savan says:


    If there a context in a cell separated by space or - then, I want the out put as true else false.

    Anybody can help on this?

  6. Syed Ibrahim says:

    Thank you. This article helped me alot.

  7. Scott says:

    Our electricity providers use very complicated formulas to calculate our monthly electricity rate. I am needing to change providers and find the least expensive company to use. I am trying to find an Excel formula to do a "look back" over my previous usage, using these current formulas.

    In simple terms, these formulas are like the following:

    "I have 10 apples. I will sell the first 5 apples for 50 cents each, the next 3 for 25 cents each and the last 2 for 10 cents each."

    Can anyone help me with a formula that would accomplish this task? In the case of the electricity providers, the variables are the cost per apple and the range of usage that falls into that particular rate.

    Thank you.

  8. Tim Carreau says:

    I want cell A, if showing a negative number, to show as zero in cell B, but if cell A is not a negative number, I want it to show as the non negative value in cell B. What formula would I use? Thanks

  9. Alex says:

    I need a formula that produces specific text if certain cells contain information (a date to be specific). The three preceding columns indicate an action being performed, and I want a column to auto populate the status.

    The status' would be "Drafted" "Reviewed" and "Finalized".

    Please advise if there is a formula that can accomplish this. Thanks

  10. Gayane says:

    I want to ask you a question. I want one cell turning into red when 30 days passed after day of payment,so i'll now that the customer have to pay after 30 days

  11. Ifeoluwa Odunbaku says:

    Overhead costs include maintenance, cleaning, and administrative expenses associated with a car rental. The Overhead for one rental transaction is $50 for an economy car and $75 for a premium car. This Overhead cost includes maintenance, cleaning, and administrative costs for each car rental. a. With this information, in the Profit Analysis worksheet, in the first cell under the Overhead label, calculate the overhead that corresponds to the number of cars rented (NumCars) using an "IF" statement as part of the formula. b. For clarification, use the "IF" statement to determine whether to use $50 or $75 times the NumCars so that the Overhead value represents the total Overhead costs for the Number of Cars (NumCars) rented in a given row.

  12. Julie says:

    Per the example shown below, how do I create a worksheet formula to look for duplicates in column B, then corresponding duplicates in column C and if true, sum amounts in D and then separately sum amounts in E? (e.g.- Jane Doe) If duplicate in column B, but not corresponding duplicate in C, then do nothing. (e.g.- John Smith)
    A B C D E
    1. DOE,JANE 234567890 5/1/2018 $84.85 $3.39
    2. DOE,JANE 234567890 5/1/2018 $405.32 $16.21
    3. SMITH,JOHN 987654321 5/1/2018 $247.13 $9.89
    4. SMITH,JOHN 987654321 6/1/2018 $247.13 $9.89
    5. JONES,JIM 736429746 5/1/2018 $369.91 $18.49
    6. ROBERTS,JUDY 103947492 5/1/2018 $555.88 $27.80

  13. John Duran says:

    Which logical formula can I use to verify that the companies haven't given employees more than 3% escalation, and if they do, what is the % applied between each year. Thank you

    Andy Garcia 2015 60.1 2016 67.31 2017 79.23 2018 79.23

    Domingo Solis 2015 45.00 2016 46.35 2017 48.66 2018 56.24

    • Doug says:

      Where the data is in O13 and N13 the formula is:
      =(O13-N13)/O13 then format the cell holding the result as a percentage with two decimal places.
      In your samples the increase for Andy from 2015 to 2016 is 10.71%. So, (2016-2015)/2016.

  14. Sanjib says:

    How to put this is formula (For calculation of Discount in a Cell on Text Basis)
    IF B1=YES then C1=A1*10/100 & IF B1=NO then C1=A1

  15. Rhonda Bruhn says:

    I am trying to create a formula that will compare a date in a column to a specific given date, if prior that that date, I want it to put the value that is stored in another cell. If the date is after either leave blank or put in a word.
    If(J47 earlier than 01/01/2017, +h47, "Active")
    I thought this would work:
    I get a message that says problem with this formula, how do I change it to make it work?
    Thanks for your help!
    Rhonda Bruhn

    • Doug says:

      I would put the 1/1/2017 date in say H47 and then compare the other dates to it. Looks like this
      IF J47 is earlier than or equal to the date in H47 then display the value in I47 otherwise display Active.
      In this example the cell holding the 1/1/2017 is being held as an absolute reference by the $. In other words, when you copy the formula up or down the J column each value is compared to the date in H47 or in this case, 1/1/2017.

  16. Francisco Amargo says:

    I created a formula that looks like: =if(d5=a12,f12,0). My problem is how will Excel compute autimaticaly if I change the value in d5 that equal the value in a13, the value in f13 will automatically be the value of the cell? Thank you

  17. Hazel says:

    I'm having trouble with this;
    if a cell shows Jul-18 (or another month) then do show a figure in another cell
    I've tried =if(d1=datevalue("01.07.18-31.07.18"),D36,0)

  18. Hazel says:

    I realise my question was not clear.
    I have forecast months in column A, prospective sales in column B and then a 12 month schedule starting Jan in column C, Feb in D and so on.
    I want a calculation to read the forecast month in column A (for example July) and then show the prospective sales value in the July column - and change to another month if I changed column A to September for example

  19. John says:

    Hi, I run a sports & social club register and each member has to do certain duties. I need to automatically assign a date a member did one of three duties by putting a code against their name in the register OP, REG, RAF into a last date column. I've tried the sumif but obviously not right for this requirement.
    Thanks for your assistance.

  20. John says:

    Hi Doug - can you give me your email and I'll send you a mini spreadsheet with the requirements on.
    Thanks again

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools -
Ultimate Suite 2018.3 Summer Offer