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

IF is one of the most popular and useful functions in Excel. 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.

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

Excel IF function - syntax and usage

The IF function is one of 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 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 (required) - a value or logical expression that can be either TRUE or FALSE. 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 (optional) - the value to return when the logical test evaluates to TRUE, i.e. if the condition is met.

    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 (optional) - the value to be returned if the logical test evaluates to FALSE, i.e. if the condition is not met.

    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

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 (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 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 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 IF statement to return TRUE and FALSE as the logical values (Boolean values) that other 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 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 IF function's syntax, let's look at some formula examples and learn how to use it in real-life scenarios.

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 examples for text values

Generally, you write an 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 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 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 with wildcard (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 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 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:

3,928 responses to "Using IF function in Excel: formulas for numbers, text, dates, blank cells"

  1. Niels says:

    Hi Alexander,
    I am trying to get a Region based on two condtions: Animal and Month.
    You choose animal in I3 and month in K3.
    The list of animals is in D6:D15, the list of months is in E6:E15, the list of regions is in F6:F15.
    Multiple animals and months can appear at the same time, in two different regions.
    However, the code I am trying to enter does not return the region as I want.

    It's a rather simple code, but I just can't seem to get it to work.

    Hope you have an idea. Thanks! :)

  2. krish says:

    i want to know the formula to calculate the diffrence in time to calculate lateness for staff.
    time in is 07:30 am
    time out is 17:00 on monday only
    time out is 16:45 tuesday to friday
    how can i calculate the overtime, lateness and for the early out.
    can you please provide me a formula.
    thanks in advance

  3. Ebighebi Rowland says:

    please I have a challenge using multiple IFS formula when dealing with strings, it returns parse error or #error. the example below:
    =IFS(J4>="ON","NOT APPLICABLE",[(J4>="FT","ENUGU DISCO")],[(J4>="LS","ENUGU DISCO")],[(J4>="PG","ENUGU DISCO")],[(J4>="TP","TSP")])

  4. Ebighebi Rowland says:



  5. Remy says:

    Question. Im trying to figure out a formula for my scheduling.
    I put the cell as AM/PM/wholeday

    AM value is 5
    Pm value is 5
    Wholeday value is 10
    What formula should i use total their hours from monday to sunday. Thank you

    • Hello Remy!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  6. Jan says:

    is it somehow possible to have both text and a formula as value_if_true/false? Simple example what I mean and what is wrong =IF(A1-B1=0;"OK";A1-B1 "PIECES MISSING")??

  7. Jon Repp says:

    I'm a layman trying to set-up an excel worksheet and hoping someone would be able to tell me how I would write the following formula for a dollar amount;
    If cell 7 + cell 9 is > cell 10, then cell 14 = cell 7; If not, then cell 14 = cell 10 - cell 9.

  8. EC says:

    I am trying to return the value of a cell if another cell is greater than an amount - it's a simple one but I cannot figure it out, can you help

  9. Janine says:

    My knowledge of Excel is basic at best. Is there a way to sort data alphabetically into another worksheet? For example, if I have a list of names on Sheet 1, can I then sort that info into other sheets broken down into parts of the alphabet? EG, Sheet2 = A - H; Sheet3 = I-P; Sheet4 = Q-Z. I'm trying to create a workload list for my staff but their work is divided by alphabet. Thank you.

  10. Adao says:

    I want a formula that check another cell if it contains certain text and just come up with the today's date if find this text.
    Eg.: ( if cell A contains ''sent to client'' the result is 27/07/2020)

  11. tusasibwe says:

    if i have multiple figures expected in acell and want to test then, how can i do it?
    example in column a, i type 1, 4, 5 9, 17, 23,and 29 an others. i want to be informed in column B that if in A there is 4, it documents y, if 5, its also y and you find that they are around 2000 figures to be tested by y. how can i set it?
    many thanks

  12. Stuart Howard says:

    I have to produce a spreadsheet for covid19 weekly testing of staff. I want to place next due date in cell after entering y in tested cell calculating 7 days ahead from date tested. A1 =date tested, B1 =y for tested C1 = due date by 7 days. Could you assist with formula

    • James says:

      Hi Stuart.

      Try the following in cell C1


      the "" will show as a blank cell Also dont forget to format cells to dates.

  13. James says:


    I am working on a table for some stats however I am wondering if there is a shorter way using an IF function to return the original value if true.

    For Example currently I use.

    Im wondering if it is necessary to copy out the original string as these may need to be added to and are getting quite long.

  14. Tracey says:

    Hello, I am wanting to create a formula that if the value is greater than 0 then the result displays the value but if is is 0 then it displays 'unknown'. Is this possible? Thanks

  15. Lena Kalyvis says:

    hi there, i'm trying to return a value if a date is between two dates, basically trying to sort my data into quarters, so next to my date column i would like a quarter column and want this column formula driven based on a table of dates. is this possible? thank you.

  16. Stuart Howard says:

    Thanks James. That did what I am looking for. Much appreciated

  17. Jeff says:

    Hi there, I am doing a survey regarding customer satisfaction level to my restaurant. So there are three satisfaction level: A, B, C. I want to find out the problem that leads customer to grade C to our services/foods with precise date. But however I still couldnt make it. Could you please kindly help me out?
    The scenario is as below:
    Customer Satisfaction Level : A, B, C
    Date: 13-08-2020
    If "Customer Satisfaction Level:B" , so it should come out with value CSL:B13082020
    I tried with IFS formula and it is as below:
    =IFS(A2="Customer Satisfaction Level:A", "CSL:A",A2="Customer Satisfaction Level:B","CSL:B",A2="Customer Satisfaction Level:C","CSL:C")
    But however, one key problem is how should I key in DATE formula to the value_if_TRUE? Maybe any other formula that can help me on it ?

    Your help is very much appreciated.

  18. JC says:

    I am trying to find the formula for the following:
    A. B. C
    1. 0. A. D
    2. 1. C. A

    =IF(A1<1, C1),IF(A1=1, B1)
    I am trying to get the cell value if the cell A1 is equal to 1 display cell B1 if value is 0 display cell C1

  19. Akshayaa says:


    I am trying a condition using IF formula, but even the blank cell is counted as value and get the output as "RED" for all the blank cells.
    My condition is "GREEN" if the score is 60 or above, "AMBER" if the score is between 50 & 59,"RED" if the score is less then 50.

    I tried the below formula; =IF(AND(BO2>=50,BO2<=59),"AMBER",IF(BO2=60,"GREEN",))))

  20. SRIKANTH says:

    Iuse this function
    so i need to add every date
    how can i easy to do this.

  21. Louise says:

    I am trying to update a cell with 'Requested', 'Received' or 'Past due' based on 3 other cells with dates in (date requested, due date and date received). I can master 2 statuses but not 3! Please can you help. If there is a date in the 'date received' column this should supersede all other statuses.

  22. Lucretia says:

    Hi, I want to write a function to change yes/no responses to numbers; So I have yes/no answers to questions and want to do some basic stats (counts etc.) and need to convert the words into numbers (e.g. 0, 1 or 2). Can you please assist?

    • Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Do you want to replace text with numbers? This is only possible with a VBA macro. You can set a value in another cell using a formula. What text does the number 0 correspond to? Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  23. Arun A R says:

    How to use if function in between the numbers. Eg 8am to 8pm peak, and 8pm to 8am off-peak. What is the formula

  24. Gareth Gittoes says:

    I have 2 spreadsheets which are orders and deliveries.
    I want to have the order sheet updated with a Y in the received column when the delivery is received.
    Can I do this through a IF formula?

  25. Chitambuli says:

    hie i wanted if its possible to use the IF function in excel to check whether in a particular column the cell have data that is in cell format or not. all the function i have tried so far give a specific date. i just want it to verify if the cell had data that in date format that's it.

  26. Junaid says:

    I want to know the formula to use for time that is greater than 3:00:01

  27. Liam says:

    My problem is i have a column full of times in 24hr time and need to categorize these times into 4 different categories in a separate column (2,3,4,5). i cant figure out the IF function to do this.
    for example the first category would be times between 0:00:00AM - 6:00:00Am would be category 2

  28. Bee says:

    I'm trying to do a check in a spreadsheet and the the IF function is giving me the incorrect result:
    If(D200=AE200,"YES", "NO")
    D200 (this is a formula value of running balance D199 + C200) and AE200 (this is sum of F200:AD200). The value is the same in each cell i.e. 6,603.16 - but gives me a NO result.
    I've tried adding in VALUE before the cell reference but this still does not give me the correct result.

    • Hello!
      Without seeing your data it hard to give you advice.
      Your numbers may differ in some decimal place. I recommend using the rounding function. Or you can perform calculations with the precision with which the numbers appear in your spreadsheet. To do this, use File-Options-Advanced-When calculating ...- Set pricision as displayed.

  29. Larry Oxford says:

    I am trying to write a formula to fill a cell (say T9) with N/A if the cell J9 includes wording "Standard Type I" or "Standard Type II". The field selections in J9 include but are not limited to
    MBCI Standard Type I 20yr
    MBCI Standard Type II 20yr
    MBCI Single Soucre III 20yr
    and so on

  30. Rajendra Bhatt says:

    i have a age list of stuents in column C . age is in yearsmonths and days i.e. 14years,11monts.20 days etc .I wants to calulate under age and over age on a perticular date i.e 01 april 2020. whoes age 1s more then 16 years "overage" and less then 15 year"underage" how can i solve this?

  31. Rajendra Bhatt says:

    i have a age list of stuents in column C . age is in yearsmonths and days i.e. 14years,11monts.20 days etc .I wants to calulate under age and over age on a perticular date i.e 01 april 2020.

  32. Epineri Vuli says:

    Please assist with this formula, especially with the last criteria. If a student is absent during test week and needs to show up on the Remark Column.
    =IF(W5>84,"An excellent performance. Keep it up!.",IF(W5>64,"A very good performance. Can still improve.",IF(W5>49,"A good performance. There is room for improvement.",IF(W5<50,"Needs a lot of improvement.", IF(W5="ab","Was absent during the test week.")))))

    • Hello!
      Add a condition to your formula that W5 is a number.

      =IF(AND(ISNUMBER(W5),W5>84),"An excellent performance. Keep it up!.", IF(AND(ISNUMBER(W5),W5>64),"A very good performance. Can still improve.", IF(AND(ISNUMBER(W5),W5>49),"A good performance. There is room for improvement.", IF(AND(ISNUMBER(W5),W5<50),"Needs a lot of improvement.", IF(W5="ab","Was absent during the test week.","")))))

      I hope my advice will help you solve your task.

  33. Henry says:

    Please i want to know how to use IF function to determine or return only each month names (e.g January , February etc) from a single column of different dates

    • Hello!
      Your IF formula will be very large. I recommend using the VLOOKUP function to select the name of the month

      =VLOOKUP(MONTH(B1), {1,"January";2,"February";3,"March";4,"April";5, "May";6,"June";7,"July";8,"August";9,"September";10, "October";11,"November";12,"December"}, 2,0)

      I hope this will help

  34. Anon Z. says:

    Hi, your explanation and example don't quiet marry up, so I will give you 2 solutions.
    9AM is 09:00 ( 9/24)
    5PM is 17:00 (17/24)
    From 5PM to 9AM is +1 day -8 hours or +16 hours (16/24) (2/3)
    #1 After 5PM show Tomorrow 9AM
    IF( ( A1-INT(A1)) > (17/24), INT(A1) +1 +(9/24), A1)
    #2 After 17:xx show Tomorrow 09:xx
    IF( ( A1-INT(A1)) > (17/24), A1 +(2/3), A1)
    # This can get really complex if you want ( 09:00-17:00 M-F, NO Change, Otherwise set time to next work day)

  35. Jo says:

    I need to work out:

    If a number of years worked is less than 5, then 0
    If a number of years worked is more than 5, then add 1 for every year above 5

  36. SHAWNA says:

    I'd like my IF statement to say:
    If AN23=KS, then type in 6/30/2021, otherwise type in 9/28/2020

    AN23 is linked to another cell and either says KS or MO
    So, the formula should place either 6/30/2021 or 9/28/2020 which are the expiration dates of a state license.

    =IF(AN23="KS",DATEVALUE["6/30/2021"], DATEVALUE [9/28/2020])

    This is what I typed and it doesn't work.


  37. Sharhonda says:


    I’m having an issue. I’m not sure if this should be a nested IF statements. The first 2 formulas represent the subtraction of dates. The dates can equal 0 to represent no days have passed/a solution was made the same day. I do not want the 0 to populate, I want the cell to remain blank.

    1. =IF(ISBLANK(N10),"",(N10-F10)) This formula subtracts & shows 0 in a following cell. Example 3 formula is calculated properly & all values are showing thereafter in other columns.

    2. =IF(N10-F10=0,"",N10-F10) This formula subtracts & removed the 0 (as desired). Example 3 formula is NOT calculated when the 0 is removed. It then throws off 3 calculations for all other columns & rows to follow.

    3. = IFERROR(IF(ISBLANK(R10),””,(H10*R10)+(U10*V10)),""). This is the formula of the calculated column that is affected perfectly when Example 1 preceded it (non desired 0 in cell) but horribly when Example 2 preceded it (desired blank cell, no 0). I believe the multiplication is throwing it off & the removal of the 0 in Example 2 is causing it. V10 is used to calculate a formula in W10 ONLY when U10 & V10 have values. They will usually be blank.

    Is there a way to put a formula in that will distinguish when to use the “+(U10*V10) like an either or? I only need the second half of that equation when U & V have values. I hope this makes sense...

  38. Sharhonda says:

    What I’m trying to express is a valid #3 Statement so that when #2 or #1 statement goes before it, it doesn’t give me an error in the last column that follows them all. The W column.

    So H&R column cells values must be calculated. At all times, which is H10*R10.

    When U&V column cells have values I have to add the all values, which is (H10*R10)+(U10*V10). (If U10 holds no value then V10 will be calculated to 0) which is my issue. I don’t want the 0 in the cell.

    Example 1 formula is column V5. When this formula is input, I see the 0. Example 3 formula is then placed into column W & the value is properly displayed.

    Example 2 formula is an alternative to Example 1 which also goes into column V5. When this formula is input, the 0 is removed in V5 as desired. However this formula clears out all values for column W, & Y which requires it to properly display & it doesn’t.

    Is there a way to nest the formula for Example 3 [H10*R10] always but ONLY when U10 & V10 have values will I use: (H10*R10)+U10*V10, so that I can use Example 2 as it is? I think it’s messing up because V10 can equal 0 & when I use a formula to not have it displayed it throws everything in all WYZ columns off. I hope this was clearer😩

  39. Sushanth says:


    Please help me to find out a formulae for below mentioned condition.

    if the value is (0-6) = need to calculate the value (number)*5%

    if the value is (7-10) = need to calculate the value as (number)*10%

    But the difficulty is if my value is 8 means first 6 want to calculate as 6*5% and remaining 2 will need to calculate as 2*10%.

    So I want the result as ((6*5%) + (2*10%))

    If any possible way to write a formulae for that. please guide me.

    • Hello!
      I hope you have studied the recommendations in the above tutorial.
      Please use the following formula


      • Sushanth says:

        Hi Alexander,

        This is not I am expecting,

        0-6 = 5%
        7-12 = 10%
        13-18 = 15%
        19+ = 20%

        For example my count is 20 means first 6 calculate by 5%, 7-12 will be calculate 10%, 13-18 will be calculate 15% ten remaining 2 will calculate 20%.

        Below the detailed condition and Please help me find out this.
        Cnt Value Result Condition
        1 50 $2.50 Value*5%
        2 50 $2.50 Value*5%
        3 50 $2.50 Value*5%
        4 50 $2.50 Value*5%
        5 50 $2.50 Value*5%
        6 50 $2.50 Value*5%
        7 50 $5.00 Value*10%
        8 50 $5.00 Value*10%
        9 50 $5.00 Value*10%
        10 50 $5.00 Value*10%
        11 50 $5.00 Value*10%
        12 50 $5.00 Value*10%
        13 50 $7.50 Value*15%
        14 50 $7.50 Value*15%
        15 50 $7.50 Value*15%
        16 50 $7.50 Value*15%
        17 50 $7.50 Value*15%
        18 50 $7.50 Value*15%
        19 50 $10.00 Value*20%
        20 50 $10.00 Value*20%
        Total $110.00

  40. HAROLD COLINA says:

    can you help me!

    What's the formula if a specific number or text will only appear in the cell?

    sample : only number 1, will appear on the cell. IF I PUT OTHER NUMBER IT WILL BECOME ERROR.


  41. Alex says:


    I'm trying to create a formula that will recognize a negative figure in an answer and if it is negative then add to it, if the answer is not a negative figure to leave the original answer


  42. Adnan says:

    Hi i am trying to create a formula which will distinguish high, critical orders as well as express air delivery methods, Now i only want TRUE in next column if both the columns have any one of the text in them.

    Order Priority Ship Mode Customers who are urgent
    High Regular Air
    High Express Air
    Critical Express Air
    High Delivery Truck
    Critical Regular Air
    Medium Express Air
    Low Regular Air

    I have been trying the simple if function to show only high or critical. but if order priority is medium or low and ship mode is express air. it shows false. kindly solve my problem.

  43. Sophia says:

    For the example on Excel IF formula examples for dates:
    - If the date is left blank, what would be the formula?

    I really hope someone can help me. Thanks! :)

  44. LynnM says:

    I'm using the following formula, where I want 30, 20, 10 to appear in the cell as money:

    After entering the formula I used the $ / currency "button" to change the cell to a dollar amount but it doesn't work. Do I need to add it into the formula? If so, how?

    Thank you!!

  45. Emma W says:

    Hi all,

    I've spent hours trying but keep getting error.

    I need a formula for the target grade a child will get depending on a test result. However (and this is the bit I'm stuck on) the result depends on when they sit the test.

    These are my options of times: Y7 baseline, End of Y7, Y8 baseline, End of Y8, Y9 baseline, End of Y9, Y10 baseline, End of Y10, Y11 baseline.

    If they get a 1 in Y7 baseline their result is MTG 3. Whereas if they get a 2 it's MTG 4. However, if they got 2 in year 8 it's MTG U.

    Hope that makes sense.

    Assessment point Result
    Y7 baseline 1 2 3 4 5 6 7
    End Y7/baseline Y8 2 3 4 5 6 7 8
    End Y8/baseline Y9 3 4 5 6 7 8 9
    End Y9/baseline Y10 4 5 6 7 8 9 10
    End Y10/baseline Y11 5 6 7 8 9 10 11

    MTG 3 4 5 6 7 8 9

    I hope there is someone out there who can help me.

    Thanks so much Emma

  46. Mo says:

    Hi, I was trying to ask you a question about adding numbers of certain Rows of a Column if the those Row has a certain keyword. For example column B has the income and Column C has the method(cash or online). and so if i were to sort the income to cash payment and online payment where it would add the column B's Certain Row if that Row's Column C has "Cash" in it.

  47. Creature says:

    Hello ,applied vlookup to the cells got N/A in multiple cells i use it as values but instead of N/A previous value to fetch and when my cell found new value formula starts from next cells (Which fetch new value as previous value)
    =IF(AND(H:H="#N/A",H3-1),"",IF(H:H=" ",H3))

  48. Melinda Hochberg-Arroyo says:

    Hi i am trying to write a formula =IF(I2-G2>=3,"HO", IF(I2-G2<4, "CO")) and i want a third option if the cells are blank it will display "-"

  49. Samji says:

    I would to get a formula that gives 1 if given data in a given cell is more than a specified number. E.g If B1>1600, then show 1

  50. Sajjad says:

    Can anyone guide me to correct this formula:


    Thank You

  51. Sajjad says:

    Can anyone guide me to correct this formula:

    =IF A2 is >=-107 and A2 is also =-104 and =-100 type High

    Thank You

  52. David says:


    Thanks for the awesome work!

    I am looking to proiduce a report which only displays a row if there is a value in a range of three (3) cells, so as to eliminate having to scroll through pages of blank data and to minimise my report length.

    Any suggestions?

    Any and all help you offer will be greatly appreciated!

    Kind Regards,

  53. Raghava Velakaturi says:

    Hi Experts,
    I am looking for the Customised display with runtime calculated value.
    E.g =IF(I3>0,"Ahead By I3%",IF(I3<0,"Behind By I3%","On Track"))
    Here i want Actual value of I3, instead of String value. Please let me know how to resolve it.

  54. Dennis says:

    Im trying to set up a responsibility sheet, cells in Colum A are names chosen from a drop list based from separate contact list. the cells in colum B will be phone numbers. But I want the phone numbers to auto populate based off the name I type in colum A. ie. if A1= Smith B1= 555-0213 . My contact list is a separate sheet in the same workbook.

  55. Jackie Hanly says:


    I am looking for a formula which count unique id numbers in multiple rows (for example 5 rows of same id number) however want it to represent 1 for the first record and then 0 for each other (so the unique id is counted as one in the data set).

  56. magala says:

    i have an issue i want to make an if statement says return 0 tax if the category is ''food'' and return 20% of the amount if the category is not ''food''. Heres how i have written my formular: =IF(C2=''Food'',0,0.2*D2)
    But it has failed to work. its saying invalid on my logical statement (C2=''Food'') but the rest are okay.

  57. Earl says:

    Good day,
    Trying to trim multiple lists of commercial names to common names. Something like xxxxxx™ yyyyyy® zzzzzzzz where xxxxxx would be the common name. Most often ™ comes after the xxxxxx and I can use =TRIM(LEFT(D3,(FIND("™",D3)-1))) but sometimes the ® will be after the xxxxxx. Trying to use an IF formula to find if there is a TM after xxxxxx gives a #VALUE! error. Is there a way to use the #VALUE! as a condition in an IF formula?

  58. Alicia Hamilton says:

    I'm trying to write a statement that expresses the following:
    If cell E2=Absent, then C2/C2 or A/A in this instance but it could be various letters in C2 or D2. If E2=heterozygous, then C2/D2 or A/G in this instance.

    • Hello!
      Sorry, I do not fully understand the task. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  59. Akshay says:

    Reference No Letter of Credit Bank - Cash 375,000
    Due Date Amount_AED Due Date Amount_AED Over Draft Bank (25,000)
    26,000 15-Oct-20 26,000 15-Oct-20 10,000 Bank Balance 350,000
    28,000 16-Oct-20 28,000 16-Oct-20 14,000
    30,000 17-Oct-20 30,000 17-Oct-20 16,000
    32,000 18-Oct-20 32,000 18-Oct-20 18,000 Salary 90,000
    34,000 19-Oct-20 34,000 19-Oct-20 20,000 Supplier Payments 85,000
    36,000 20-Oct-20 36,000 20-Oct-20 22,000 Dewa 25,000
    38,000 21-Oct-20 38,000 21-Oct-20 24,000 TR 186,000
    40,000 22-Oct-20 40,000 22-Oct-20 26,000 LC 100,000
    42,000 23-Oct-20 42,000 23-Oct-20 28,000 Totl Expences/Liabilites 486,000
    44,000 24-Oct-20 44,000 24-Oct-20 30,000 Reserve (136,000)
    46,000 25-Oct-20 46,000 25-Oct-20 32,000
    48,000 26-Oct-20 48,000 26-Oct-20 34,000
    50,000 27-Oct-20 50,000 27-Oct-20 36,000
    52,000 28-Oct-20 52,000 28-Oct-20 38,000
    54,000 29-Oct-20 54,000 29-Oct-20 40,000
    186,000 100,000 20-Oct-20 286,000

    Need to know the date when my reserve becomes 0 so that we can maintain cash flow based on above example

  60. Jesse Carroll says:

    I have the following formula put in and it works: =IF(G2="PAPR", "X")
    All cells in the column (Column F) contain dates, except where no date is needed, then and X is required, based on if the cell to the right in Column G contains "PAPR" or not. My issue is when I go to drag the formula down in Column F, the dates in the cells disappear and turn to FALSE. How can I apply the formula to all cells in Column F containing dates without the dates disappearing? Thanks!

  61. Will says:

    I have the following formula =IF(COUNTA(C8:C12)=5,IF(AND(MIN(C8:C12)>0.999,MAX(C8:C12)<4),"Pass","Fail"),"INCOMPLETE")
    It looks at the data gives "incomplete" if any data is missing, if all Pass, I get a "Pass" result, and if one Fails I get a "Fails" result. However, I would like for the Result to change from "incomplete" to "fails "as soon as one data point Fails. I don't want to wait to take all 5 data points to change from Incomplete to Fails, and if no data points are failing I would like for it to stay as incomplete.
    I hope I explained myself correctly.

  62. Bill Geraghty says:

    Is there a way to write a formula that says that if a cell has a certain name in it, that I want a series of cells populated (for example - if A2 = SCHOOL then fill B2; AA2)?

  63. Greg says:

    2. Bob receives a bonus of $50 from the d20 manufacturer each time his sales exceed $500 in a quarter. Write a conditional statement (IF) in cell B11 that will display 50 if the d20 sales are greater than $500 and 0 otherwise. Once you have the formula correct, copy it into C11-E11.

  64. BETH says:

    Hi, please help. what's the formula - I have three columns, if these three has a value, it will appear as completed, if either column 1 and 2 with column 3 has a value, it will appear completed, if only column 3 but there's no value for column 1 and 2, it will appear almost completed.

    another, how can i count the cells with formula but only has value?

    Hope you can help me.

    • Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

      • beth says:

        column1 column 2 column 3
        938,080 1 6001371
        0 1 6001481
        938,080 0 -

        like this one sir, if there's a value in column 1 or 2 then also with column 3, its will appear as completed. But if there's a value in column 1 and 2 and there's none in column 3, it will appear as not completed or vice versa, if column 3 has value and column 1 and 2 has none, it also appear as not completed.

  65. Rutger says:

    Hey all. I would like my formula to do the following:

    If a value in 'Table135 Column 2' equals a value in 'Besteltabel Column 1' and there is a value in 'Table135 Column 1' and there as a value in 'Table135 Column 7' and there is no value in 'Table135 Column 8' Then I want it to say yes... if not, I want it to say no.

    The current formula I have doesnt work, but should be along the lines of what Im looking for. Can someone help me out??

    Current formula:
    =IF(Table135[Artikelnummer]=(Besteltabel[[#All];[Artikelnummer]])&(Table135[Datum bestelling]=TRUE)&(Table135[Datum levering]=FALSE)&(Table135[Aantal besteld]=TRUE);"JA";"NEE")

    Thanks in advance.

    • Rutger says:

      Oh forgot to mention the following:

      Table 135 - Column 2 = Artikelnummer
      Table 135 - Column 1 = Datum bestelling
      Besteltabel Column 1 = Artikelnummer
      Table 135 - Column 7 = Aantal besteld
      Table 135 - Column 8 = Datum levering

  66. Callum says:

    Hello There,

    Im having trouble coming up with the Formula to calculate that If

    A1 has a Invoice Issue Date in and B1 Is to have the Payment received date in but is currently blank.

    how can I have it show me in C1 how many days it has been since the invoice was sent. Up until the point when I have the payment date and then I would like it to stop the count and show me how many days it was between start and finish.

    so to basically have an open counter in days until a final date is put in.

    fingers crossed one of you can help as this has been frying my brain all morning I'm sure its not as complicated as I'm making it out but it has gone beyond me. Many Thanks in advance

  67. MOHAMMED IMDAD says:

    I have multiple documents which need to be diffracted using formula

    I want a formula for the Below type.,

    If i have the below documents types., help me with the formula in "__"

    CE It should show "PAYMENTS"
    CM It should show "PAYMENTS"
    RV It should show "INVOICE"
    RR It should show "INCENTIVE"

    Please help me..Appreciate your time and efforts.

    • Hello!
      Sorry, it's not quite clear what you are trying to achieve. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  68. Maye says:

    Hi, I'm looking for an IF formula that will return HAPPY if the score is greater than or equals 15 and SAD if it is less than 15.

    Thank you.

  69. Sunder Raj says:

    Is there a formula to find the cell value between two numbers?

  70. Ken says:

    Thanks for the post.

    I'd like some help on my personal project that involves calculating savings on my savings account. What I like would be to auto calculate on specific dates when my pay comes in and the excel calculates it automatically with any input form me. Thanks.

  71. Natalie says:

    Hope someone can help!
    I am trying to run a formula that will place text in a column based on a upcoming date in another column. For example: If Go live is within 3 business days, (this is column j, with a date of Nov 1) then place "at risk" in column k. Another example. If Go live is within 7 business days, place "upcoming" in column k. The problem is the dates are fluid and I don't want to have to change the datevalue every time the date changes.
    Hope this makes sense.

  72. AD says:

    hi, i need help to search in column about value and return raw number.

  73. AnnaLynn says:

    I am working on a COVID spreadsheet. I have two columns. One for a Symptom On Set Date and one for an exposure date. I need a 3rd column for the Return Date. This date is +11 or +15 based on if it was onset of symptoms or exposure. I want to create an IF Then statement that is something like...
    =IF On set Date is true then add 11 to the cell of on set date or if exposure date is true then add 15 to give me a return date.

    I tried =IF(J2_True,"J2+11",K2_true,"k2+15")

    J2 is my On set date and K2 is my exposure date. It obviously is incorrect. Can you help me? Thank you.

  74. Nina says:


    Hope you are well, wonder if you can help me;

    I am trying to calculate; if CELL 1 has a date > than the date in CELL 2 then calculate days over (currently using =IF(F7>L7,F7-L7,0) but i want to add, if CELL 1 is blank then still calculate days over using todays date - CELL2.

    The current formula only works out the days over if a date has been inputted in CELL 1, but i need to use AND/OR condition but i cant get it work. (Date in cell 2 is another formula counting 1 year on from another date used)

    Hope this makes sense...

  75. girish says:

    can you help me for this formula
    A Coolum contains 1) Acc repairs 2)running repairs B coolum contains value in c coolum contains Value if
    i want formula in d coolum if a running repairs value of b coolum if a coolum contains acc repairs then B-C

  76. Chathu says:

    If the 1st 2 digit of the number in a cell is 11, i need to be rename as Import in another cell and if it is 10 , it has to be rename as Local , that is my requirement..please help....

  77. Sahibzada Asad Ullah says:

    S.No Vehicle-Reg-No Rept-Date Rept: Time Entry Date In Time In Date Out Time Out
    1 GLT5739 02-Nov-20 19:20 03-Nov-20 21:50 04-Nov-20 23:40

    I want to make below report from my above excel sheet.
    1.In side the plant More than 12 hours Vehicles.
    2.Out side the plant More than 12 hours Vehicles.

  78. Afonso Osorio says:


    Sorry, I am a bit confused with the nested Isnumber on IF (Example 3) in text values.

    I understand ISNUMBER function in Excel evaluate if a cell contains a numerical value or not, and returns True or false.

    IF(ISNUMBER(SEARCH("deliv",C2)), "No", "Yes")

    Now, In this case, from inside, I would expect the SEARCH to return all "deliv*", and feed into ISNUMBER and get checked if such value is numeric then input (True or False) into IF function.

    You have explained well what SEARCH/FIND functions perform in evaluating text values. I do understand that in this particular case the ISNUMBER is a sort of "helper" (bridge).

    Could you please explain the outcome of this expression ISNUMBER(SEARCH("deliv",C2)) and how it is evaluated at the end..

    Thank you for your valuable time.

    • Hi Afonso,

      I think you have explained almost everything yourself :) I will just clarify about the SEARCH function. It returns the position of the first occurrence of a given character or substring within a text string. In example 3, cell C2 contains "Delivered", so SEARCH("deliv",C2) returns 1. If C2 contained "item delivered", then SEARCH would return 6. If C2 contained "deliverable delivered", then SEARCH would return 1 again (the position of the first found match). For cells that do not contain "*deliv*", SEARCH returns a #VALUE error.

      Like you said, ISNUMBER evaluates the SEARCH output. If it's numeric (i.e. "deliv" is found in C2, no matter in which position), ISNUMBER returns TRUE, and IF applies value_if_true ("No" in our case). If SEARCH results in an error, ISNUMBER returns FALSE, and IF applies value_if_false ("Yes" in our case).

      I hope I answered your question. If something is still unclear, please feel free to ask.

  79. Rohan says:


    After doing the IF syntax and getting the 1's or 0's as a result, how do we get to know that how many number of 1's and 0's are there if there are lot of values to be checked.

    Thanks in advance!


  80. ROHIT says:

    I have a variable creatinine which contains a numerical value. I want to show the variable if the value is less than 4, else if it is more than 4, then display 4.

    =IF(Creatinine > 4, 4, Creatinine)
    This formula is displaying 0 & 1 if the condition is false or true respectively.
    What am I doing wrong?

  81. Alannah says:


    I've been racking my brain trying to work this formula out but am at a loss and was hoping you'd please help.

    What I'm trying to achieve is IF the cell equals >11 its a YES, IF its 11,"YES","NO", IF(D9="","UNKNOWN))

    Thanks in advance!

  82. Alannah says:

    Greater than 11 its a YES
    Less than 11 its a NO
    The cell is blank its a UNKNOWN

  83. Rob says:

    I am working in a simple spreadsheet but would like for the whole IF-Then argument equation (if True) be returned AND display the answer.
    " x [0.06600] = 0.06600" assuming the column containing the x has a 1.
    "x [0.18200] = 0.72800" if the column containing the x had a 4.
    "x [0.00030] = 0.05100" if the column containing the x was 17.

    So, I'd like for the whole text phrase of "x [0.00030] =" to appear along with the answer value "0.05100" as indicated

  84. Michelle says:

    Hi, help?

    If A1= Yes and B1 = Yes then return Complete
    If A1= Yes and B1 = No then return Overdue
    If A1= No and B1 = No then return In Progress


  85. Sudhendra says:

    Hi I wanted to use Farmuale as IF(E3=E2),CONCATENATE(B2&B1),B1) But reult showing up error. Can some one help inthis regards ?

  86. Irina says:

    Добрый день, пожалуйста, помогите, есть такая таблица , и мне надо в последнем столбце "категория роста" вывести значения- высокий, средний или низкий в зависимости от пола и роста,в самом низу градация
    я написала выражение- =IF(AND(D15 = "ж", E15>170), "высокий") ,IF(AND( D15= "ж", E15 178),"высокий"),IF(AND( D15= "м", E15178 см >=168 см <=178 см 170 см >=150 см <=170 см <150 см

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 :)
Ultimate Suite for Microsoft Excel
Merge two tables
Combine Sheets
Merge Duplicates
Consolidate Sheets
Copy Sheets
Merge Cells
Vlookup Wizard