Comments on: How to use IF function in Excel: examples for text, numbers, dates, blanks

IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading →

Comments page 12. Total comments: 2999

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

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

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

    1. Hi, Reena,

      I'm afraid there is no easy way to get the output you need using a formula. You'd better use a macro. I'm sorry, we can't help you with this, please try to find the solution in VBA sections on mrexcel.com or excelforum.com.

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

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

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

    1. =IF(D177<0,D177*F177,"")

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

    1. Hello,

      Please try to enter the following formula in cell C1 to get the result you need:

      =IF((B1-A1)>2,"No","Yes")

      Then just copy the formula down along column C.

      Hope this will help.

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

    1. =IF(A1>8,(A1-8)*100+8*68.75,A1*68.75)

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

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

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

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

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

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

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

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

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

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

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

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

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(OR(AND(ISBLANK(A1),A2>TODAY()),A1>A2),"PAYMENT LATE","PAYMENT ON TIME")

      Hope this will help.

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

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

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

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(CELL("format",A1)="D1","X","Y")

      Hope it will help you.

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

    1. Hello,

      If I understand your task correctly, please try to enter the following formula in cell B1:

      =IF(WEEKDAY(A1,2)=5,30,0)

      Hope it will help you.

  16. I could really use some help since the formula I'm using doesn't seem to be working. I have a drop down box in column A and column B with numbers ranging from 1-5. I'm creating in column C the "quadrant" it would fall in depending on the numbers selected in column A and B.
    For example. If A1=1 and B1=1 then C1= Quad1

    This is the formula I have to input in C1 but nothing happens... help please! I have no idea what I'm doing wrong.

    =IF(AND(A1="1",B1="1"),"Quad1","")

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(AND(A1=1,B1=1),"Quad1","")

      Hope this will work for you

  17. Hello i have a list of over 272 phone contacts in 272 cells and i would like to put all of then in one cell with ease. How do i go about it?

    1. Hello,

      Please try to solve your task with the help of the Merge Cells tool which is a part of our Ultimate Suite for Excel. You can download its fully functional 14-day trial version using this direct link.
      After you install the product, you will find Merge Cells in the Merge section under the Ablebits Data tab.

      Hope this will help you with your task.

  18. I am going to create a tracker for my files with the following status "New File", "Pending", "Done", and "File sent to Author". What I want to do is, the STATUS Column should be generated automatically with the above status when the file is inputted on the excel file. I have here 5 examples of data. I cannot attach a file, but please take time to add this into excel so that this will be cleared and understand the format of this tracker. Every column has a header and next are the data. There are cells are intended to be blank depending on the status of the file. The image of the tracker is horizontal.

    First Column

    From month of
    Dec
    Dec
    Dec
    Dec
    Dec

    Second Column

    Product Code
    ABC
    DEF
    DEF
    GHI
    GHI

    Third Column

    File
    RO123
    RO456
    RO456
    RO789
    RO789

    Fourth Column

    Check-out Date
    N/A
    7-Dec
    7-Dec
    7-Dec
    7-Dec

    Fifth Column

    Print Check-out Date
    N/A
    N/A
    N/A
    N/A
    N/A

    Sixth Column

    Copy Check-out Date
    N/A
    N/A
    N/A
    N/A
    N/A

    Seventh Column

    Print/Copy/ Check-out by:
    N/A
    Marie
    Marie
    Marie
    Marie

    Eigth Column

    Check-in Date
    7-Dec

    7-Dec

    7-Dec

    Ninth Column

    Check-in by:
    Marie

    Marie

    Marie

    Tenth Column

    Requested by:
    Omar
    Omar
    Omar
    Omar
    Omar

    Eleventh Column

    Status
    New File
    Pending
    Done
    File/s sent to Author
    Done

    Thank you,

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  19. Hi,

    Need make a If statement where, if the cell is blank it should return 'X' cells value and if the cells alphanumeric character length is 12 it should return 'X' cells value.

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =IF(ISBLANK(A1),B1,IF(LEN(A1)=12,C1))

      Hope this will work for you

      1. Thank You Gennady, yes the formula works.

  20. I need to make an IF statement where one column is the expiration date and one is the current date and If the expiration date is 10 days less than the current date, then the expiration date cell will turn red. Any help would be appreciated. Thank you.

    1. Hello,

      Supposing that the expiration date is in column A, please try to do the following:

      1. Select cells with your data.
      3. Click Conditional Formatting -> New Rule.
      4. Select the "Use a formula to determine which cells to format".
      5. In the Formula field type the following formula:
      =TODAY()-A1<=10
      6. Click the Format button to set the format you need.
      7. Click OK.

      We have an article on our blog that describes how to use conditional formatting in Excel. Please have a look at it.

      Hope it will help you.

  21. Can someone help me with a formula
    if a purchase is $500 or more they can make the first payment in 180 days otherwise if less than $500 they make the first payment in 30 days. the result needs to show the new payment date

    1. Hello,

      Please try the following formula:

      =TEXT(IF(A1<500,B1+30,B1+180),"mm/dd/yyyyy")

      where cell B1 contains a date value, e. g. 1/30/2018

      Hope it will help you.

  22. Hi,

    I've a situation where I have 4 cells to enter data in which If I enter data in Cell A then either B, C or D should have some entries or any of them should have entries. Can you please help me with this logic.

    Thanks
    Ramakrishna

    1. Hello,

      Please create a custom Data Validation rule for cell A1 using this formula:

      =OR(NOT(ISBLANK(B1)),NOT(ISBLANK(C1)),NOT(ISBLANK(D1)))

      Please check out this article of ours to learn how Data Validation works.

      Hope it will help you.

  23. Hi. Good day!

    What would be the formula I can use for the details below.

    if Column A is "Corporate A", then amount is 25,000;
    if Column B is "Corporate B", then amount is 55,000;
    if Column A is "Academe", then amount is 15,000;
    if Column A is "Individuval/Startup", then amount is 5,000;
    if Column A is "Support Sector", then amount is 60,000.

    Your help will be much appreciated. Thanks!

    1. Hello,

      Please try the following formula:

      =IFS(A1="Corporate A",25000,B1="Corporate B",55000,A1="Academe",15000,A1="Individuval/Startup",5000,A1="Support Sector",60000)

      Hope it will help you.

  24. Hi want to ask if a have an array of data, each of my samples have a row of value (few column of descriptive value). and I want to use IFBLANK() to check the each of the row to give it a "complete"(if all are filled) or "incomplete" (if it got any empty cells in the row) data.

    How do i do it? IFBLANK(C2:X2) for example, it just gave "false" to the row but it still has an empty cell.

    1. Hello,

      Please try the following formula:

      =IF(SUMPRODUCT(ISBLANK(C2:X2)*1)>0,"blank","non-blank")

      Hope it will help you.

  25. I am having trouble finding a formula for defining the payment deadline for invoices.
    If the invoice is dated before the 15 of a month it should be paid by the 15th of the following month, otherwise it should be paid before the 30th of the following month.
    I've tried combining IF with DATE but it returns the formula as text...

    1. Hello, Mara,

      Please try the following formula:

      =DATE(YEAR(EOMONTH(A1,1)), MONTH(EOMONTH(A1,1)), DAY(IF(DAY(A1)<15,15,EOMONTH(A1,1))))

      Hope it will help you.

  26. I need to have a formula for the following; exp, column D, if b1=1, then enter vc; if b1=2, then enter vce if b1=3, then enter pp, and so on up to #5
    thank you so very much for helping me

    1. Hello, Wanda,

      Please try the following formula:

      =IFS(B1=1,"vc",B1=2,"vce",B1=3,"pp",B1=4,"value4",B1=5,"value5")

      Hope it will help you.

  27. Hi. I have a question on the IF logic: state the status in a column as COSTLY if total expense is above $20,000, or FAIR if total expense is less than $20,000 but greater than $10,000, or MAINTAIN if total expenses is below $10,000

    1. our value in A1 cell

      =IF(A1="","",IF(A110000,A120000,"Costly"))))

  28. Hello, I am trying to get a cell AB3 to show "complete" and if not to show "pending" when Cells on the spreadsheet H3,N3,T3, and Z3 are all showing "complete". I do not have the slightest idea how to write the "IF" formula.

    Any suggestions are appreciated greatly!

    1. If(and(H3="COMPLETE",N3="COMPLETE=,T3="COMPLETE",Z3="COMPLETE"),"complete","pending")

      I done by mobile but its correct check

  29. In cell A cell B
    apple 1x
    grape 1x
    strawberry 1x
    grape 2x
    melon 1x
    grape 3x
    what function do i need to complete my cell B automatically
    tq....

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  30. What would be the equation for 1000 ≥ certain cell block ≥ 501 =30%? For instance if certain cell block equaled 5000 the answer would be 150.

    1. But if certain cell block was 800 then the answer would be 90.

  31. is there a formula for multiple logical test
    example. I wanted to place a certain word into a certain cell if it contains an specific text. thank you

  32. Hello
    I need help to output the following:
    if given a list of id numbers and the 5th number represents sex i.e 1=male 2=female
    which formula can I use to find the sex
    eg given the following IDS:
    001329876
    123415672

    1. Hello, Gofa,

      the formula below should help:
      =IF(RIGHT(LEFT(A1,5),1)="1","male","female")

      Feel free to take a look at this article to learn what LEFT function does, how it works.
      Hope this helps!

  33. Hi,

    here's my formula right now:
    =FLOOR(MAX(C19,G19), 0.125)+$K$8

    what I want to do is that, i want to add an IF that if C19 and G19 are the same value, the formula will automatically deducts 5 from G19.

    any help will be much appreciated.

    Thank you

    1. Hi, Leo,

      if I understand your task correctly, the following formula should help:
      =FLOOR(MAX(C19,IF(C19=G19,G19-5,G19)),0.125)+$K$8

      Hope this is what you need!

  34. Hi Ablebits.com Team,

    I am looking for help what kind of formula to resolve this condition.

    When A1= coal and B1= coal, then assign as coal
    however, when A1= coal and B1= 0, then assign as 0

    1. Hi, Daniel,

      you need to create a nested IF formula with AND logic in it:
      =IF(AND(A1="coal",B1="coal"),"coal",IF(AND(A1="coal",B1=0),0,""))

      Please note that if neither of these conditions are met, the cell with the formula will remain empty.

  35. Hello, hope you can help!

    I have a column(A) with a drop-down menu with 7 different options ('Meeting', 'Private Party. 'Weddings', etc)That run on a calendar year.

    I have another tab where I have each option in a column (B), and next to it (C), I would like to have the total numbers of 'meeting', 'Party', 'Weddings' (from column A) within that Year (So a total SUM)

    How can I use the Sun formulas for a Word variable?

    does this make sense?

    1. Hello, Zsofia,

      I believe you could use the following formulas to count the words:
      =COUNTIF(A:A,"Meeting")
      =COUNTIF(A:A,"Private Party")

      and so on.

      Please check out this article of ours to learn how COUNTIF works.

  36. I was wondering if anyone could help me out with an if statement.

    I have a figure in cell B5 I want to run an IF/OR statement on it to do the following:

    If B5 <5 then use the value in X5, OR if B5 = 5 AND 10, then use the value in X7

    Any help would be appreciated.

    1. Hi, use the below Formula i think it will work

      =IF(B5<5,X5,IF(OR(B5=5,B5=10),X7,""))

  37. Dear All,

    scenario as below.
    Price
    A1 B1
    1 YES
    2
    3
    4
    5
    i want to use formula wherein B1 Column populate automatically as "YES" to which the lower price is.

    Please help

    1. HI, IF(A1<=1,"YES","")

  38. Hi,

    I am trying to pull a number based on the input.

    IF(B24510030045, then result should be as C27 & B2 valus is >100<299 then C27...

    Can anyone help me to solve the issue for me..Thanks in advacne

    1. Hi,

      I am trying to pull a number based on the input.

      =IF(B245100300500,C6)))) IF the input value is B2 = 5 then result should be as C2 & B2 value is >100<299 then C4...

      Can anyone help me to solve the issue for me..Thanks in advance

  39. Hi,

    I was hoping to get some help with my formula below:

    My cell E32 is interchangeable, could be 50, 45, 32, 0, e.t.c. I want the returned value to be in two (2) decimal place if condition is met in the formula. instead of just displaying 1, 2, 3, 4,5 in my formula it should give me exact value in 1 or 2 decimal places. i tried to add .0 to the formula but eachtime it keeps disappearing.

    =IF(AND(E32>0,E322.2,E324.4,E326.6,E328.8,E32<=11),5,"Out of Range")))))

    Thanks

    1. Sorry see correct formula:

      =IF(AND(E32>0,E322.2,E324.4,E326.6,E328.8,E32<=11),5,"Out of Range")))))

  40. Hello,
    I'm using excel 2010.
    In cell D2 is a date. F.e. 07/08/2017
    In cell G2 is a value F.e. 38
    I would like to use a formula in cell I2, which will give me the value from cell G2, if the date in cell D2 is greater or equal than 28/08/2017.. I am currently using the formula: IF(D228/08/2017;G2;IF(D2<28/08/2017;""))
    So normally in my example, the outcome should be "" because the date in cell D2 is smaller than 28/08/2017 but it doesn't...

      1. Natalia,

        I have been struggling with date ranges within IF formulas, but your response to Glenn helped me greatly. Many thanks.

  41. I have used a simple IF statement to identify Pass/Fail conditions.

    IF(I4>1, "FAIL", "PASS"). As long as I4 contains actual results, this works fine.

    But sometimes there is text in I4 (see note, etc.). Since the text is not >1, the result should be PASS, but I'm getting FAIL.

    So I turned it around: IF(I4<=1, "PASS", "FAIL"), but the results are the same, FAIL when there's text in I4.

    Can you explain this?

    (Still) using Excel 2003 (I hate the ribbons).

    Thanks

    1. Hi,

      You need an additional condition that will check if the values in I4 is number. Please try the formula below:
      =IF(AND(ISNUMBER(I4),I4>1),"FAIL","PASS")

      You can read here how AND logic is used in formulas.
      Hope this helps.

  42. Hi

    I have value in a cell(A1) like "01-USA" (This is fixed Cell Value)
    Another Cell(A2) value is changing as per country selection but sometimes value comes same as in fixed cell. Please note both cells have text value only.
    so I want following results

    If A2 value is same as A1 then result like 10-5 =5
    If A2(02--Canada) value is different from A1(01-USA) then result must be Blank

    Thanks in advance for your help.

    1. Hi, Surya,

      as far as I can see, you have all the conditions to build a nested IF. Please check this tutorial to learn how it's made. There are lots of examples as well.

      Also, in case you don't know how to show if the values are not equal, I'd recommend you to look through this article as well.

      Hope these pieces of info will help you solve the task.

  43. Hi Sveltalana ,
    I have 2 cells with dates(A2, B2) , i would like B2 to highlight if it is larger than 180 days / 6 months than cell A2

  44. what would be the formula

    if the sum total in C10 is less than or equal to 300 the cell should show 300 but if C10 is greater than 300 then it should pick the C10 value.

    can you help me in this

  45. I am trying to produce a number in a cell.

    The number must come from column F when then column C and column N match a specific text.

    The formula I have tried only give FALSE results.

    =IF(AND('Sheet1'C:C="XYZ", 'Sheet1'!N:N="ABC"), 'Sheet1'!F:F, "N/A")

    Any help would be appreciated.

    1. Hello, David,

      I'm afraid this formula won't work like this.

      If you need to return only one number from each F cell, you need to use a separate formula for each separate cell, like:
      =IF(AND(‘Sheet1’C2=”XYZ”, ‘Sheet1’!N2=”ABC”), ‘Sheet1’!F2, “N/A”)
      (you can place the formula into one cell, and copy it down the column)

      Ii you need to perform some mathematical operation with the found values, you need to enclose the whole IF formula into COUNT, or SUM, or AVERAGE, or some other function depending on the operation you need. In this case it is an array formula. It is entered by pressing Ctrl+Shift+Enter on your keyboard instead of just Enter.

      Hope this helps!

  46. So I am trying to make a call schedule for my work and am having trouble creating a formula that will help me. What I am looking to do is have the Initial Call date (A) and a column for the call back date (B). I want excel to fill in the call back date for me (B) for a certain length of time per my customers buying level.
    Ex: If customer is "gold level" (C) and the initial call date (A) is 1/1/2017 then call back in 30 days or 1 month (2/1/2017) (B) or,
    If customer is "silver level" (C) and the initial call date (A) is 1/1/2017 then call back in 60 days or 2 months (3/1/2017) (B).
    Can this be done? Thanks for the help!

  47. Mine seems simple. I need

    Formula in H10
    If C10 contains "Payment" Then enter the sum that's in G10 into H10
    So if it's a payment I need the sum to move right one cell
    and yet I cant figure it out.

    Thanks

    1. I DID IT!

      =IF(C10="Payment",G10,"0")

  48. I am looking to join two cells together but separate the two values by a comma into a new cell.Can someone supply an =if statement that would help for this?

    1. Hi there! I do this often to combine two cells with only a space separating and it's super easy. You simply add a new column and within that new column, you type in "=G2&","&H2" . The G2 & H2 simply represent the cells you want to combine. What you have in between the quotations is what is entered in between the combination, etc. Then do a drill down down the column for all others and you're all set!

  49. Sir/Madam,
    While awarding grades to particular mark range, I want a '-' where the marks are zero . I applied this formula but it doesn't seem to work.

    =IF(BX9>90,"A1",IF(BX9>80,"A2",IF(BX9>70,"B1",IF(BX9>60,"B2",IF(BX9>50,"C1",IF(BX9>40,"C2",IF(BX9>32,"D",IF(BX9<=32,"E",IF(BX9=0,"-",IF(I8="AB","AB"," "))))))))))

    Kindly guide me.
    Thanks in advance .
    Malar

    1. Dear Malar as I have understood IF function it has three parts. Logic (greater, equal, less than etc), true or false parts. What you are trying to do is to put 10 IF functions in one cell. So I think it will not work. If you want to put "-" where marks are zero use this
      IF(BX9=0, "-", "")
      if BX 9 is zero it will return - value in cell other wise it will be empty.
      or use IF(BX9=0, "-", BX9) it will return original value if its not zero.
      As I understood you what you are trying to do is not possible with IF function. You have to find other function for this.

  50. I am trying to add a column of money if the number opposite = the number 50

    =IF((J44:J55=50,sum(I44:I56),0)) - but this doesn't work

    Tks in advance.

    1. =SUMIF((J44:J55,"50",I44:I56)

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)