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 the IF function 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")

    If you don't want your IF formula 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 if 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")

    If you put a comma after the value_if_true argument, your IF function will returns 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

    If you want 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)
    or
    =IF(B1>10, TRUE)
    an example of the IF function that displays logical values TRUE or FALSE

    Note. If you want your IF formula to return TRUE and FALSE as the logical values (Boolean) 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 to "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. Get IF to perform a math operation and return a result

    Instead of returning certain values, you can make 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.

IF function examples 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 IF formula for text values 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. IF formula for text values 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 functions 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 to 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.

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

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,913 Responses to "Using IF function in Excel: formulas for numbers, text, dates, blank cells"

  1. Mahendra Nayal says:

    Hi,

    Can we use IF formula with Text formula.
    e.g.if Cell A1 coming date 12/12/2017 like this.and in B1 we need a value. if A1 is friday (not mentioned in cell A1 only date is mentioned) so it is coming 30 otherwise 0. condition is for only friday. Please help me on this. Can you please reply me on this mail "mnayal98@gmail.com"

  2. Ramesh says:

    Dear sir

    IN Excel i want to use If and Date functions formats
    example
    A B
    1 05/12/2017 if(A1=DD/MM/YYYY,"X","Y")
    2
    3 06/12/2017

    DD/MM/YYYY this may be any date formulae to executed
    kindly suggest
    Regards
    Ramesh

  3. A T M Kamal Hossain says:

    from a spreadsheet data i want to set if condition for setting a range if the data is > 5.3 than the value will be 5.3 if less then 5.3 than that would be as it was. pls let me know.

    regards

    kamal

    example

    7.2 5.3
    3.2 3.2

    • DAVID says:

      A B C B
      5.3

      Solution
      5.3
      Do this:
      =If(A1>5.3,5.3,A1). This means that if the value in cell A1 is greater than 5.3, return the value 5.3 or else (if below), return the value in cell A1.
      Iam learning too.
      Thanks

  4. Neil_B says:

    Hi,

    Been searching but am unable to find a solution to my particular problem and would like to seek help as it's driving me nuts!

    Three conditions need to be met, but I'm unsure about the sequence and correct syntax:

    1. =IF(A1>A2,"PAYMENT LATE") - A1 & A2 contain dates. PAYMENT LATE displays in A3.

    Conditions 2 and 3 are nested together. I'm trying to determine that IF cell A1 is BLANK AND the date in A2 is > than today, then "PAYMENT LATE", otherwise, "PAYMENT ON TIME" to be displayed in A3.

    Hope you can help.

  5. Martin says:

    I'm trying to get a formula that will calculate results for annual, monthly, fortnightly and weekly expenses. If A2 is Annual divide A1 by 12, but if Fortnightly multiply A1 by 26 then divide by 12 etc.

  6. SYED says:

    Hi,

    On IF formula, I need to enter a range say 1% to 24%, I need "Won", then 25% to 50%, I need "Entered"

    I have typed =IF(N3>1%,<24%,"Won"....but it is not working..

    Please help..

    • DAVID says:

      I am learning too. But try this:

      =If(N3<=24,"WON",IF(N3<=50,"ENTERED"))

      OR

      =If(N3="","",if(N3<=24,"WON",IF(N3<=50,"ENTERED","")))
      This means, if N3 is blank, return blank in cell, if N3 is less than or equal to 24%, return "WON". If cell N3 is less than 50% return "ENTERED" and blank if above 50%

      Thank you.

      • Tina says:

        Thank you David. I was able to adapt your formula for my sheet. =IF(E5="","",IF(E5>97,"Pass","Fail"))

        I was looking everywhere online for a formula that would indicate a pass or fail for a > or < Value and that could recognise blank cells. Couldn't find anything that would work until reading your post in this forum. You are a life saver!!!!

  7. K MANIKANDAN says:

    MY IF CONDITION IS BASED ON TEXT...HOW CAN I PUT A FORMULA

    • Hi!

      Here are a few examples of IF formulas with text-based conditions:
      Excel IF function examples for text values

      • hubid says:

        Hey Svetlana

        so I am using a if statement for the task i am doing and it is not working. basically i have over 100 villas and some of the villas have multiple units in them. I have assets within those units so what i want to be able to do is i have a table that says villa 1 has units 1 and 2 and villa 2 has units 3 and 4 etc. If I have 10 assets in unit 1 and 5 assets in unit 3 what formula should i use to automatically choose the villa those assets are in

  8. YMCA says:

    do you know the formula that i can use if example, 121 is negative and i wrote (40 - 2 = 38) then how can i write (-121 - 2 = -123) the subtract sign to addition sign?

    • Hi!

      I am not sure I understand the question. In Excel, you enter negative numbers as usual by typing the minus sign in front of them. For example, you you type =-121-2 in a cell (with no spaces), and press the Enter key, you will get -123 as the result.

  9. William says:

    Hi,

    If I need to make a calculation for a start date and a finish date to display completed when both cells have the dates entered, a blank cell if neither start/finish has dates entered and open if only start date is entered but finish is not.

    How would I do this, I've scoured the internet and I cannot resolve the issue.

    much appreciated

  10. Justin says:

    I think you're looking for this:

    A1 is Start Date
    B1 is Finish Date
    C1 is where formula entered to display Started, Completed, or Blank

    =IF(A1&B1="","",IF(B1"","Completed",IF(A1"","Started")))

  11. Andrew Fisher says:

    Hello - i need help with a function.. will try and explain it below;

    i have 3 columns on my table and I would like the value in the 'Coat 2' column to equal the value (a paint name in this case) in the 'Coat 1' column - IF the value in the 'No/Coats' column is 2

    i.e.
    No/Coats Coat 1 Coat 2 Coat 3

    2 Acrylic Acrylic
    1

  12. Aparna Arun says:

    Can I get IF THEN to work for multiple values? e.g. If A1 5, <10, then B3 = 1 etc. Its more than just A1/10, theres a range. If this function doesn't work, is there any other function I can use?

  13. Owen says:

    i need a function that can return text for me for a range of figures
    to From 1.00 To 2.40 Excellent
    Over 2.40 To 3.00 Very Good
    Over 3.00 To 3.60 Good
    Over 3.60 To 4.00 Fair
    Over 4.00 To 5.00 Poor

    • Daniel Nelson says:

      Owen, I was able to use the following function:
      =IF(A2<=2.4,"Excellent",IF(A2<=3,"Very Good",IF(A2<=3.6,"Good",IF(A2<=4,"Fair",IF(A2<=5,"Poor")))))

      where "A2" is the cell being evaluated.

  14. Owen says:

    How can i use if to return the words for the range of figures as below
    From 1.00 To 2.40 Excellent
    Over 2.40 To 3.00 Very Good
    Over 3.00 To 3.60 Good
    Over 3.60 To 4.00 Fair
    Over 4.00 To 5.00 Poor

  15. tanvir says:

    i would like to see the date when the number shows 0 in another worksheet. please help

  16. Sabbir Ahmed says:

    Hi, I want to apply formula for below chart.
    such as, if full test then will add 180 with report out date, if psr then will add 90 with report out date.pls help to share formula.

    REPORT OUT DATE VALID DATE COMMENTS
    16-Nov-17 14-Feb-18 PSR
    11-Aug-18 7-Feb-19 full gpt

  17. Jean B Rossol says:

    I need help with a formula. Checking a TEXT field for one of 3 conditions:
    If cell = "Joe", display 3000
    If cell = "any other name" display 1500
    if cell is blank leave blank

    Something like this: =IF(F12="Joe",3000), If (F12 IsBlank 0, 1500)

    cant get it to work

  18. Adam says:

    I want to use an =IF(AND(TODAY() function to highlight rows that represent current week data.

    Each row has A date for beginning and ending of the week so =IF(TODAY() falls between x and y date, I would like it to highlight that row but my formula isn't work.

    B26 is the beginning of the week date
    C26 is the ending of the week date

    =IF(AND(TODAY()>=B26,TODAY()<=C26),$A26:$Q26)

  19. prahlad kumawat says:

    how to creat if formula i am slect month and auto fill date in slect cell

  20. SURESH T NAIR says:

    A B

    JANUARY - 1830
    JANUARY - 1430
    FEBRUARY - 300
    MARCH - 200
    FEBRUARY 500

    I NEED A SUMMARY TABLE THROUGH IF CONDITIONS LIKE

    JANUARY = 3260
    FEBRUARY = 800
    MARCH = 500

    • Hello, Suresh,

      You can fulfill you task using our Consolidate tool which is a part of Consolidate Worksheets Wizard. You just need to select a sheet with your data table on step 1, choose the Sum function and specify to use the left column label for consolidation on step 2 of the Wizard, and finally choose where you'd like to place the results.

      Feel free to install the fully functional 7-day trial version of the add-in and see how easily you can get the result you need. You can download the trial using this direct link.

      If you have any questions, don't hesitate to ask.

  21. Sadri says:

    How can I do the first cell to be written the sequence of the letter and second cell to become the letter with a formula?
    ie.
    cell1=1 cell2=A
    cell1=5 cell2=E
    cell1=8 cell2=H

    Such a formula would be ok:

    A. B
    1. =if(A1=1, B1=A)

    If necessary I can send you our data.

  22. Yana says:

    How to do formula for below cases.

    I have planned and Actual Dates (both have start & end date).

    (i) IF there is a value at the (H37) end date (actual) = COMPLETED
    (ii) IF (E37) end date planned = (H37) end date actual = ON TRACK
    (iii)IF only have(G37) start date (actual) = WORK IN PROGRESS
    (iv)IF (H37) end date (actual) is later than (E37) end date planned = DELAYED

    ELSE put as NOT STARTED

    =IF(H37,"COMPLETED",IF(H37=E37,"ON TRACK",IF((H37-E37)>1,"DELAYED",IF(G37,"WIP","NOT STARTED"))))

    Item (i) to (iii) works but not for item (iv). Can someone help please...

  23. Manendra says:

    I need to calculate the amount of drivers

    Duration is 8 hrs if he done more than 8hrs we pay 100/hr must with in the 8 we pay 68.75/hr

    Example if he done 10hr a day we pay 8*68.5 +2*100

  24. kshitija says:

    I want a formula where
    Date in A column if equal to or less than B column, it should say Correct. If B column is greater than 2 then it should return "NO"

    A B C

    29-Jan-18 30-Jan-18 Yes
    29-Jan-18 1-Feb-18 No as the difference is more than 2 days
    29-Jan-18 2-Feb-18 No
    29-Jan-18 31-Jan-18 Yes

  25. Sean says:

    Hi,

    I have a set of four cells that return either text or a number. I have a warning that arises if ALL of the cells are text, otherwise no warning (simple if statement - =IF(AND(ISTEXT(B5),ISTEXT(C5),ISTEXT(D5),ISTEXT(E5)),"WARNING:",...). For the false argument, I need something very specific. I need to produce another warning if ONLY ONE of the cells listed is a number, but as soon as two or more are numbers, then I want to do something else. How do I say, "Do this when ONLY EXACTLY ONE of the cells is a number"?

  26. Saqib Javaid. says:

    What is the functionality of Marcor in excel, how macro creation help us in sorting data?

  27. Brian says:

    Can I return a text statement and a cell value in the return value of an If statement? For example if the logical test is false can my false value be a text with a cell value? i.e value_if_false is "Please change parameter to D19" where D19 is a cell value.

    syntax =if(D18=D19,No change necessary, Please change value to D19)

    Where D19 is the actual cell value that gets displayed and not the text D19

  28. KHUSRO says:

    Greeting sir
    Kindly explane what is the meaning of this formula =if(A12=false,C12,A12*C12)

    Thank you

  29. KHUSRO says:

    Hi
    Kindly explane what is the meaning of this formula =if(P12<=0,R12,P12*R12)

  30. Akram says:

    Greetings,

    please guide me on how to create the following formula: if the value of the cell is in (minus) then multiply by the value of a different cell. Let me know if it is possible.

    Thanks alot

  31. S Khan says:

    Hi,

    in cell A1 value is 1000. In cell A2 value is 12. now I want formula in Cell A3. Formula would be like that.

    IF CELL VALUE OF A2 12 BUT 18 BUT <=24, THEN A1*3%.

  32. Thilakarahtne says:

    Please explain how can i have three answer using two columns as follows,

    Ex: in 1st column = column A and B if >0 = "yes", if not "NO"
    if it is a figure only in Column B the answer should be " Loading",

  33. Kaztec says:

    I'm trying to populate a manning layout.
    what formula would work across multiple tabs?
    Example:
    cell 1 = 1 for shift
    cell 2 = 4 for position
    cell 3 = 6 for area

    cell 1 & 3 moves name to spreadsheet in correct tab and cell 2 is what cell in that tab it populates.

  34. Joe Cribari says:

    Coll A Coll B Coll C
    Y Y
    N N
    N Y

    I am trying to write an IFs formula for Coll C, that returns a Y if Coll A & B are Y, a blank if Coll A & B are N, and a N if Coll A is N and Coll B is Y.

    • Hello, Joe,

      Please try the following formula:

      =IF(AND(A1="Y",B1="Y"),"Y",IF(AND(A1="N",B1="Y"),"N",""))

      You haven' t mentioned what the formula should return if Coll A is Y and Coll B is N. Currently the formula returns nothing in this case. If you want to see N or something else in column C when this condition is met, you can just add IF(AND(A1="Y",B1="N"),"N" before the double quotes at the end of the formula above.

      Hope this will help.

  35. Bob says:

    I track mileage and other data on an Excel spreadsheet. I know the round trip mileage for each city I go to. I simply want it to autofill the round trip mileage of the city I type in. For example:

    If, in row 10, I type W.Roxbury in column A, I want Excel to autofill 58 in column B. If, in row 11, I type Sturbridge in column A, I want Excel to type 116 in column B, and so forth for each individual row.

  36. Reena says:

    Hi,
    I have 3 columns and I have to create another column by concatenating :

    Last Name First Name DOB Alt

    Addison Ashley 10/12/2012 AddAO12
    Aguilar Jayden 7/2/2013 AguJ702
    Adkins Skyhe 12/28/2012 AdkSD28

    So in the Alt column I know how to get LastName and firstName but need to display month in letters as O for 10(october) , D for 12 (December) if birthday month is 2 digits and if single digit month the single digit has to be returned.

    Thank you

  37. sudeep says:

    Hi Team,
    I need a formula for automatic calculation of age cycle..! Below mentioned example for your reference..!
    I have to convert data from below mentioned age format to cycle (below 20, 21-25, 26-30,30 above.)

    25yrs7m8days
    31yrs11m3days
    18yrs4m21days

    • Hi,

      Suppose your data are in column D, please try to enter the following formula in cell E1 and then copy it down along the column:

      =IF(VALUE(LEFT(D1,SEARCH("yrs",D1)-1))<=20, "20 and below", IF(VALUE(LEFT(D1,SEARCH("yrs",D1)-1))<=25, "21-25", IF(VALUE(LEFT(D1,SEARCH("yrs",D1)-1))<=30, "26-30", "30 and above")))

      Hope this is what you need.

  38. Zeff says:

    Hi,
    I'm new to IF function, I'm trying to write a formula but there's a problem on it, I need help.
    A = Date 1
    B = Date 2
    C = Status
    D = No otherwise overdue

    =IF(AND(A<TODAY(),"Overdue","No"),IF(AND(B<TODAY(),C"Approved & Closed","No","Overdue")))

  39. ALEA says:

    Hi,
    I am new to IF Function. I am trying to create formula to find the Turn around time (tat).

    A COLUMN = DATE START
    B COLUMN = DATE END

    IF DATE END IS BLANK THEN TAT FORMULA IS TODAY()-DATE START
    THEN IF DATE END IS WITH DATE IT SHOULD BE DATE END MINUS DATE START

    WHAT WILL BE THE FORMULA FOR THIS USING THE IF FUNCTION?

  40. yogendra says:

    Hi,

    I want to use 'if' formula, I have 5 slabs 500000 to 999999 = 1%, 1000000 to 1499999 = 2%, 1500000 to 1999999 = 3%, 2000000 to 2490000 =4% & 2500000 to above = 5%
    so how to use IF formula, pls help me...Thanks

  41. maggie says:

    need a formula for , any hours over 8 in cells D thru H would be added to the overtime cell (K)

    D E F G H total reg ot

    8 8.75 8.00 9 8 41.75 40 1.75

  42. Alex says:

    I am trying to count the number of days between two dates but want the return value to be 0 is there is no date in one or both of the cells. So far I have;

    =IF(OR([@[Date Required]]"",[@[Date Passed to Ops]]""),NETWORKDAYS([@[Date Required]],[@[Date Passed to Ops]],-1),0)

    But I am getting a #NUM! error, what have I done wrong?

  43. Ursula says:

    Hi, I'm trying to figure out a formula to list the date in the text. If B25 is a date value, then if it has passed to display a comment that lists the date. When I use this text, it shows Date B25, but I want it to be entered as an actual date value or to come out black if it's not expired. With the below formula it displays Contract expired on, DATE(25). Instead of listing the date.
    =IF(B25<DATEVALUE("03/18/2018"), "Contract expired, DATE(B25)", "")

  44. kamlesh mistry says:

    my quay for 1 person 28 days present so we have give to extra bonus amount 10 Sapphose he is not 28 days so how can give me bonus amount

  45. Jester says:

    Q: Please help me translate this into formula. I'm really having hard for the then statements that are equal to computations.

    Statement: << If Hours >

    Letter Assignments: Hours is B4 | Rate is C4 | Gross Pay I4

  46. joe petix says:

    Hi
    I'm trying to type a if statement for text with the following criteria:
    if a cell begins with ="GA" then "General Assortment"
    if a cell begins with "7" or "9" and is 10 characters long then equals "Planogram"
    If cell is between "1" TO "9999" then store layout module

  47. Ramachendir says:

    Sir
    I want formula for
    100g 36pkt
    Open stock 10cb
    Production 5cb+45pkts
    So for total stock 45pkts-36pkts difference to be converted into cab(10+1=11) and at pkts 45-36=1 pkt for doing this how to put formul
    Regards
    Ramachendir

  48. Nikshit says:

    I am trying to put a formula where the date of one column is more than or equals to todays date then it should display as "not expired" and if the date of one column is less than todays date then it should display as "expired".

  49. Sean says:

    Trying to come up with a formula that says, if the number in cell A1 is greater than 1, but less than 500,000 the result is 120.

    Similarly, if the cell in A2 is greater than 500,001, but less than 1,000,000 the result is 200.

    Thanks in advance.

  50. Shamyka says:

    I am trying to put a formula for multiple spreadsheets, where on spreadsheet A the date indicates something as completed and a blank spot indicates something is incomplete using - spreadsheet B contains the date/blank spot. So essentially spreadsheet A is meant to be a summary of spreadsheets B-G (B-G have the data).

  51. sandeep says:

    HI

    If TEXT IN A1=B1=C1 THEN THE SAME RESULT IF TRUE ELSE FALES

    PLEASE HELP HOW TO USE IF CONDITION

  52. Marie says:

    I need help with this formula: =if(B5:E11>100,"Yes","")

    I wanted to put the values if any from the test in the G column. I tried the formula in cell G5; selecting the G5:G11 in the G column and typing the formula in formula bar; and selecting B5:G11 with If from Autosum list.

    I get an #Value! message in each cell selected of the G column every attempt.

    What am I doing wrong and how can I fix it please?

  53. Ida says:

    Chargeable Wt| |Handling & Doc| |Custom Clearance| |Transport Charges|
    335.5

    I wanted to put the values once chargeable wt key in but the formula that i try to input result is 0...

    actually i want to set like this - if chargeable wt less than 233.33 charges charge to us MYR21.00 but if move than 233.33 they will charge us MYR0.09 per kg.

    Can you help me..

  54. kalashree says:

    Hi,

    I have a question if there a same question but I have different answer for same as below which formula can I use.
    data Answer
    no water Vavle off
    no water empty
    no water abc
    no water cyzdf

  55. Joe Borbone says:

    Hi Team, You guys/gals do a great job. I hope you can help me.
    I need to check for duplication in a list of names. Column A contains the primary name. Column B through H contain other contacts. I need a way to check if any of the other contacts equal the primary name.
    Example: Adam, Bob, Jason, Rick, Susan, Adam, Ray, Zak. (the response here would be a fault.
    I tried: +IF(B2:H2=A2,"OK","Fault") but I get an error message. I also tried: +IF((B2:H2)=A2,"OK,"Fault")
    I know I could compare each of the other contact cells individually, but that would be inefficient.

  56. Adrian says:

    Hi, please help me whit a formula for my need

    I have a cell that can be text or number ( AH99). I need to make an IF AH99 is any text, then return 0 and if not, multiply that number from AH99 whit another cell ( AI99) and return result

  57. Sara says:

    Hi,
    If i have a list of text and I wish to enter the blank cells with text and leave the other cells with there original text is there a formula for that.
    I have created a True false column as I thought this might help but am stuck. I can enter the true statement to add the text that but the false argument I am stuck on.
    Regards Sara

  58. RGamak says:

    Hello I need some help with my excel. I have a default value of 3 on A1 then if B2 is greater than 0, then i need to deduct 1 from A1. Thanks guys

  59. Tim says:

    Can I use cell/text formatting as a condition for an IF expression? For example IF the content of a certain cell (A1) is formatted as strikethrough THEN contents of the current cell (B1) = a value (1)

    Thanks for your help.

  60. ONG says:

    Hi, I need help in creating a logic formula.

    Job date : 14 Mar 2018
    Job time : 18:00
    Job completed date : 15 Mar 2018
    Job completed time : 09:00

    I need to calculate the incentive.
    job time 19:00 to 23:59 : $5
    job time 24:00 to 07:00

  61. ONG says:

    Hi, I need help in creating a logic formula.

    Job date : 14 Mar 2018
    Job time : 18:00
    Job completed date : 15 Mar 2018
    Job completed time : 09:00

    I need to calculate the incentive.
    job time 19:00 to 23:59 : $5
    job time 24:00 to 07:00 : $10

    How do I write the logic formula?

  62. Sudha says:

    Hi Team,

    I am trying to find a substring from a string of URLs

    example : I have column (a) with codes "xhd2424" and coloumn (b) with URLs "http://www.xyz/today_same-xhd2424". I am trying to see if the code in column (a) exists in column (b). If so I want to print "true" or "false" in a different column.

    I have been using the following formula " =IF(ISNUMBER(SEARCH(A2,B:B)), “Yes”, “No”)" it seems to work on some spreadsheets and for some i get an error "#Name". I am not able to figure out the issue with the formula. Only that the code in the URL doesn't always be at the end, it is embedded in the middle for some.

    Please help.

  63. Mita says:

    How to write logical formula for the following ?

    If sales is 101% to 104 % of Target , incentive will be 1% of Target value
    If sales is 105% and above Incentive will be 1.25% of Target value

  64. Deyeno says:

    I can get this working as individual formula's

    =IF(C27="P", "1")
    =IF(C27="DO", "0")

    What is the best way to combine into one argument.

    Thanks.

  65. Deyeno says:

    Please ignore my first post, as I have worked out the formula, but realised I cant sum the values in the target Cell

    So this what I am trying to do now.

    If C27 = P then S27 = 1
    If C27 = DO then S27 = 0

    Is the above possible.

    What is the best way to combine into one argument.

    Thanks.

    • Doug says:

      Looks as if this will need a nested IF formula. Ablebits has a good description of how to use these as well as similar conditions. Search "Nested IF formulas in Excel" here in Ablebits.

  66. MariusP says:

    Thank you for the lovely examples of using the IF() function. I'm going to use this for a lesson on teaching the IF() function.

  67. Lauren says:

    Hi! How can I create a formula for the following?

    =IF(H9="S4309","",IF(H9="S4310","",IF(H9="S4311","")=I9+365))

    So, if cell H9 equals either S4309,S4310 or S4311 then cell K9 will be blank. Otherwise, it will populate an expiry date based on cell I9.

    Thank you for your help!

    • Doug says:

      I believe this is what you're looking for.
      =IF(OR(H9="S4309", H9="S4310",H9="S4311"),"Blank",I9+365)
      I put the word "Blank" in there to test it. You can use "". I formatted I9 as Date and H9 as Text.

  68. KHASNIS says:

    i want to use that if condisation

    like when i type company name it has to show the GST no.
    i have so meany clients so thats way mail me or msg me how can i use that condisation 8197938637

  69. Sridher says:

    I need formula in excel

    in entire row if same number - count only 1 and other show as zero

    111 - show as 1
    123 - show as 1
    124 - show as 1
    124 - show as 0
    125 - show as 1
    125 - show as 0
    126 - show as 1
    125 - show as 0

  70. Mara says:

    if I want to find if C1 is between 200 and -200) put "Lab" and if G2 is "PD25088" put "GEO"

    • Doug says:

      Your question is unclear because the connection between the two outcomes is unclear. I can't figure out the logic.
      However, the first part will require =IF(AND(C1-200), "LAB", "No LAB").
      The second is =IF(G2="PD25088","GEO","No GEO").

  71. bali says:

    hi
    i am making price list
    i need to do in b column if number is same in b column so in in k column result will be plus in l column
    exam
    1 A B --------- K L
    2 1 --------- 25.50 38.25
    3 1 --------- 12.75
    4 2 --------- 05.75 17.20
    5 2 --------- 11.45

    SO WHAT SHOULD I WRITE IN L COLUMN....

  72. bali says:

    hi
    i am making price list
    i need to do in b column if number is same in b column so in in k column result will be plus in l column
    exam
    1 A B --------- K L
    2 1 --------- 25.50 38.25
    3 1 --------- 12.75
    4 2 --------- 05.75 17.20
    5 2 --------- 11.45

    SO WHAT SHOULD I WRITE IN L COLUMN....

  73. bali says:

    hi
    i am making price list
    i need to do in b column if number is same in b column so in in k column result will be plus in l column
    exam
    1 A B --------- K L
    2 1 --------- 25.50 38.25
    3 1 --------- 12.75
    4 2 --------- 05.75 17.20
    5 2 --------- 11.45

    SO WHAT SHOULD I WRITE IN L COLUMN....

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 - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard