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 22. Total comments: 2999

  1. Please can you help me?

    need to calculate if a cell (D58) has inbetween 6 and 12 in then it should equal F7 (which is £25.58).

    The cell before i need to know if the number was 5 or under so i =SUMIF(D58, "<=5",F6:F6) which is working fine.

    After i have figured out the 6 - 12 one i need to do the same for 13-18 and then one for 19 +

    1. Hi Laura,

      >if a cell (D58) has inbetween 6 and 12 in then it should equal F7 (which is £25.58).

      You can use the following formula:

      =IF(AND(D58>6, D58<12), F7, "")

      And for multiple conditions, you can use nested IF's:

      =IF(D58>19, F9, IF(D58>12, F8, IF(D58>6, F7, F6)))

      If D8 is 5 or under, return F6
      If D8 is between 6 and 12, return F7
      If D8 is between 13 and 18, return F8
      If D8 is 19 +, return F9

      1. Thanks

  2. Hi all,

    does anybody know if a formula exists that could have with the following scenario....

    if I have a cell with the text "19mm & 12.5mm" can I get another cell to display the same value which is shown in another cell.
    for example...
    I have a cell which is linking to a sheet and returns the calculation of "38" - I basically want that same figure (whatever the calculation is) to displayed in the box below it IF a certain cell displays the text "19mm & 12.5mm"

    1. Hi Daniel,

      Supposing that A1 is the cell with "19mm & 12.5mm", and B1 contains the result of the calculation you want to display (e.g. 38). Then you can use the following formula:

      =IF(A1="19mm & 12.5mm", B1, "")

      1. HI..
        I want to use if function with RAND() Function...

        I want to use =RAND()*7+$C$12 in cell C11
        if cell C12 is greator then zero...
        otherwise Result should be zero in cell C11

        help me..

        1. Hello!
          If I understand your task correctly, the following formula should work for you:

          =IF(C12>0,RAND()*7+$C$12,"")

  3. * display sales order after 2-jun,product name beginning with letter "G"and unit sold in excess 100.
    * display all details ; sales date on 1-jan or 3-jan and number of unit sold less then 150.
    * list those records sales date and 2-jan ; unit sold less then 150 and product name ending in letter "est".
    * display all records for countries in state of Florida with words north or south in country name and land area are more than 500.
    * display those records for countries in the state of California or Colorado with population between 200000 and 300000 and having unit of more than 100,000.

    1. please giving answers to me....

  4. If B70 1600 then "Scania" or if value is 1000 then "Merc 1323" or if B70 is 450 then "merc 818" and so on.

    Please help

    1. Hi Shelly,

      Our blog engine often swallows the "<" and ">" symbols, sorry for that.

      If my understanding of the task is correct, the following formula should work a treat:

      =IF(B70<450, "merc 818", IF(B70<1000, "Merc 1323", IF(B70<1600, "Scania", "")))

  5. I need H5 to be multiplied by 6% if > A1 and multiplied by 7% if > than A2 and multiplied by 8% if > A3.

    1. Hi Kristal,

      Here you go:
      =IF($H5>$A$1, $H5*6%, IF($H5>$A$2, $H5*7%, IF($H5>$A$3, $H5*8%, "")))

  6. thanks! - sent you the info via email

    1. The problem was with percentages entered as text strings. Emailed you back the fixed sheet.

      1. Thanks so much for the help! appreciated!

        Now i have a question around "if i can't get the text to percentage on the sheet to work unless i double click the box and click on the accept icon" (tick box image).

        Is there a way to change format quicker on the sheet.

        I've tried selecting the column, going to number format and changing to percentage - but then i still have to click in each box to accept it, before the formula works. Trying to see if i can save myself time and do it with a shortcut.

        Thanks

        1. In the worksheet you sent to us, I fixed the percentages in the following way:

          - Select column H (the Percentage column), press Ctrl+H and replace % with nothing (leave the "Replace with" box empty and click the Replace all button).

          - In some empty column in row 2, enter the formula =H2/100, copy the formula down to other cells, copy the column with the formula, then select column H, right click and select Paste Special > Values. The result would be decimal numbers like 0.75.

          - Apply the Percentage format to column H.

          Done!

          1. It was and great thank you - it fixed it perfectly!!

  7. Hi Svetlana

    Thanks but unfortunately that doesn't work either, tried it and even if i have a 10% on the probability - it still brings up the number.

    1. Hmm... this is very strange. The formula works just fine for me. If you can send us a sample workbook, and we will try to figure this thing out.

  8. Hi

    I'm trying to get a formula to work this is the detail:

    Column G = sales
    Column H = probability
    Column J = status

    I'd like to get a formula to work if its status is open and is above 75% then it returns the sales number - i have this but it won't work
    =IF(AND(J300="Open",H300 >75),G300,"")

    1. Hi Ash,

      Your formula is correct except that you omitted the percentage :)

      =IF(AND(J300="Open",H300>75%),G300,"")

      If you'd rather not use the percentage char in formulas, then it should read H3>0.75.

  9. So I entered the formula to have a cell name itself the workbook name which is a date. The other cell that I have turns into the day of the date. But since I have done = it only reflects the date not the day.

    1. Hi Justin,

      Sorry, I am not sure I can follow you. If the problem is just with displaying the date rather than the day of the week, select the cell, press Ctrl+1 to open the Format Cells dialog and select the desired date format there.You can find the detailed steps and lots of examples in our Excel Dates tutorial:
      https://www.ablebits.com/office-addins-blog/change-date-format-excel/#custom-date-format

      If you have a different issue, please clarify.

  10. HI there,

    can you help me with this project?

    IF (A2 >26, A242, A263, A284,A2<104),4 "")

    I appreciate your kind help

    1. (A2 >26, A2>41, A2>42, A2>62,A284,A2<104),4 "")

      1. Hi Fahad,

        Sorry, I cannot understand the conditions. Can you elaborate please?

  11. A1= Level 33
    B1>50%=YES and B150%=YES and C1<50%=NO

    1. I am not sure I fully understand the logic. Nevertheless, here's the formula as per your conditions:

      =IF(B1>50%, "YES", IF(C1<50%, "NO", ""))

  12. I need help with finding the sum of the following:
    If there is text in C10 and C11, I need there to be a value of 1 for each to calculate in F4, otherwise = 0.

    1. Hi Elaine,

      I can suggest the following formula:

      =IF(AND(C10<>"",C11<>""),2,IF(OR(C10<>"",C11<>""),1,0))

      The formula returns 2 if both C10 and C11 have values in them, returns 1 if one of the cells has a value, and if both cells are blank, 0 is returned.

      If you are looking for something different, please clarify.

  13. Column E has a product Names (X and Y) with the corresponding Value ($ amt) in Column G, If the product name is X I want a formula to return the value as (X*70/100) or Else Blank. The result is expected in column H. Please Assist.

    1. Hi!

      Try this one:

      =IF($E2="X", $G2*70/100, "")

  14. Need help,

    I've a data in d2:d in which sometime there might be empty also(blank).So with reference to these I want information of a2:a but if d2:d have data I didn't want information of a2:a, I only want a2 information if d2 is blank other wise next skip a3 if its same then a4 and so on?

    1. Please help me in this regard.

      1. Hi Insu,

        I am not sure I can follow you. So, let me check if we are on the same page. You want the formula to return a value from A2 if D2 is blank, right? If so, then you can use the following formula:
        =IF(D2="", A2, "")

        If you want to pull a value from D2 if it's not empty, then use:
        =IF(D2="", A2, D2)

        1. Thank you

  15. I want to calculate if A1>=1.5, but <=2 then yes

    1. Hi Tshepo,

      Here you go:
      =IF(AND(A1>=1.5, A1<=2), "yes", "")

  16. Hi, Svetlana

    Below condition is not working

    =IF(OR(A1=c1,B1*F1),IF(A1=C2,B2*F2),IF(A1=C3,B3*F3))

    Can you please correct me.

    Column A Column B Column C Column F
    B81234 16 B91456 $8,995.00
    B81345 19 B81234 $4,887.50
    B91456 27 B81345 $5,391.00

    Regards
    Manish

    1. Hi Manish,

      Try to put it this way:

      =IF(A1=C1, B1*F1, IF(A1=C2, B2*F2, IF(A1=C3, B3*F3, "")))

  17. Hi Svetlana, I am using the formula below look for a specific that could be listed in the cell range listed below, such as the name Darryl that name could be clustered in with multiple names in one cell, But I need to count the name as one. I hope that makes sense

    =COUNTIF(M95:M106,"Darryl", "Darryl Sally George")

    1. Darryl, how did you get a response for svetlana so qwick? I have tried since last week.

      Maybe you can help.
      Hi, Svetlana.
      I have in mind a (maybe an IF) formula I want implement.
      In a cell I have a date of 6-17-15, in one cell (oh,say E7) when we started tracking an item as "Open". Cell F7 (7/7/15) would be the date we closed it. Cell G7 would tally the days it was open. If we closed it on 7/7/2015, the duration open would be 20 days and the formula would be simply be =F7-E7. Correct?
      But, let's say we had not (closed it) filled in the Cell F7 yet and it is blank to visually show it as outstanding. How would I express the formula in Cell G7 to keep a tally of the open days. Then when we fill in the (or closed it) cell G7, it will correctly fill in the open days?

      1. Hi!

        I am sorry for overlooking your question. The notification never reached my inbox :(

        If my understanding of the task is correct, you need a formula that subtracts E7 from F7 if both cells have dates in them, and if there is no date in F7, then E7 is subtracted from today's date. If so, the formula is as follows:

        =IF(F7="", TODAY()-E7, F7-E7)

        Please correct me if I am missing something.

        1. It works great. Thank you.

    2. Darryl,

      The COUNTIF syntax allows for only one criteria. To count partial matches, you can use a wildcard char, like this:
      =COUNTIF(M95:M106,"Darryl*")

      Or =COUNTIF(M95:M106,"*Darryl*") if the name "Darryl" may be preceded and/or followed by other characters, e.g. "Mr. Darryl George".

  18. I am try to count how many "Y"'s in a range of columns and convert that into a numeric value and place that value in a different cell.

    Please help

    =IF(SUM(D95:D106)="Y", "1")

  19. Using Logical "IF" formula to determine if date in one cell is < date in another cell =IF(A1<B1,True,False). Returns the correct answer, however when I try to copy it to multiple rows....it does not! If however I go to each row and click the Insert Function menu option...it will change the answer to the correct one! Can't seem to figure it out! Please help

    1. Hi Scarlet,

      Usually copying a formula by selecting the cell and dragging down the fill handle works without a hitch. Are you coping it to other cells in the same column, or somewhere else? For example, the formula is in C1 and you need to have it in, say, C1:C100?

  20. I am trying to add all of the values in column E that have the corresponding number 53017 in column G. I tried this formula but it didn't work.

    =IF(G1:G53=53017,SUM(E2:E58))

    Can anyone advise?

    Thanks!

    1. Hi Lisa,

      You can use the formula similar to this:
      =SUMIF(G1:G53, 53017, E1:E53)

  21. Hi, how do I get the value if true to be another formula? I have a list of customers (column A), and a list of corresponding dates (column B), I want to set the value if true to be, If cell A1 = Customer x, out put to be date (b1) + 2 days.
    By having the formula as =IF(A1=Customer,"B1+2","") the output is B1+2 whereas I want it to display the date in B1 + 2 days?
    Thansk

    1. Hi Chris,

      Just remove the quotes enclosing B1+2 because they turn any expression into a text string, and apply the Date format to the cell containing the formula:
      =IF(A1="customer",B1+2,"")

  22. Can you please help me with following formula

    i have a calculation in Cell A and another calculation in Cell B

    in another cell i want to determine results as following

    if value in cell A and Cell B are greater than or equal to .5 give me true otherwise false

    if values in cell B is blank take value from cell A only and provide results

    1. Hi Kamran,

      You can use a formula similar to this:

      =IF(AND(A1>0.5, B1>0.5), TRUE, IF(AND(A1>=0.5, B1=""), TRUE, FALSE))

  23. Hi Svetlana,
    I am referencing a date in cell L57 which is looking at a due date and my formulae works is written as:
    =IF(L57>NOW(),"'OK'","OVERDUE")

    My problem is if I enter a date in another cell,(Say L58)(that tells me that for example, a payment has been made) how can I add a multiple ÍF' function so that it that it references L58 also (which will have a date entry), and then correct cell L57 to read ÓK'' indicating visually that payment has been made.

    Your help would be much appreciated.

    1. Hi Jason,

      You can embed an OR statement in your formula, e.g:
      =IF(OR(L57>NOW(),L58>NOW()),"'OK'","OVERDUE")

      1. Hi Svetlana
        Thankyou so much for your response, however it still does not change the cell response to read OK (when a date value in cell L58 has been entered)

        It is as if it does not overwrite the rule in the first statement.

        Any ideas??

        1. Jason,

          It thought the formula should return OK when the date either in L57 or L58 is greater than the current date (i.e. greater than NOW()).

          If you want it to return 'OK' when either a date in L57 is greater than NOW() or any date is entered in L58, then the formula should read as follows:
          =IF(OR(L57>NOW(), ISNUMBER(L58)),"'OK'","OVERDUE")

          1. Thankyou so much Svetlana, that worked!

  24. Hi Svetlana! I'm trying to figure out an IF statement using dates. Something like, IF(B2=older than three years than NOW(),"Too old", "New"). So simple but I can't figure it out. Thank you!

    1. Hi Michael,

      You can use the DATEDIF function to calculate the difference between 2 dates in years. For example:

      =IF(DATEDIF(B2, TODAY(), "y")>3, "too old", "new")

      1. This worked perfectly! Thank you so much :D

  25. I am trying to get this formula to reference one more cell. if the additional cell contains a date, the formulated cell will be blank.

    any suggestions?

    =IF(ISBLANK(Response_Due_Date),"",IF(Response_Due_Date<TODAY(),"PAST DUE",""))

    1. Hi!

      I think you can add an OR statement to the 1st logical test:

      =IF(OR(ISBLANK(Response_Due_Date), ISNUMBER(additional_cell)), "", IF...

  26. Hi Svetlana,

    Need a little help. I have 4 text from which i have to choose, if 1st is present then others are false, if 1st missing and second is present then 2nd is true, if 1st and 2nd is missing and 3rd is present then 3rd is true and if 1st 2nd 3rd is missing then, if 4th is present then 4th value is true.

    How do I use IF formula here so that I can get my desired result only from many?

    1. Sorry for the confusion.

      Right now I am using below formula but it seems to be working only with first column.

      =IF(F6" ",F6,(OR(AND(G6=" ",OR(AND(G6" ",OR(AND(H6=" ",OR(AND(H6" ",OR(AND(I6=" ",OR(AND(I6" "))))))))))))))

      Example is mentioned below for your reference

      Column1 Column2 Column3 Column4 Result
      A B -- D Sould be 'A' (A is first present text)
      -- -- C -- Should be 'C' (C is first present text)
      -- B -- D Sholud be 'B' (B is first present text)
      -- -- -- D Should be 'D' (D is first present text)
      -- B C -- Sholud be 'B' (B is first present text)

      1. Took a little time but I have solved this problem.

        Thanks so much for your help for creating this amazing blog though.

  27. This formula is working for me: =VALUE(IF(E12="X","660",IF(E12="","0")))
    but I need to make 2 additions:
    E12="1","660"
    E12="2","660"

    When I've added them I get a #VALUE! entry in the cell. Can you please help me with the right formula? Thanks so much!

    1. Hi Shannon,

      I am not sure why you use the VALUE function in the formula. The following one works for me:
      =IF(OR(E12="X", E12=1, E12=2), 660, IF(E12="", 0, ""))

  28. Hi Svetlana,
    How do i set the formula if i want to calculate the bonus based on 20% of their basic salary, and the salary is cap at maximum $6000. Meaning to say if someone basic salary is at $8000, still he will received the bonus at $6000x20%=$1200. And if someone basic salary is at $5000 then i would be $5000X20%=$1000. Your advice please. Thanks

    1. Hi Low,

      Supposing column A is salary, you can use a formula like this:
      =IF(A2<6000, A2*20%, 6000*20%)

  29. Hai Svetlana,
    Still waiting for your valuable reply for given below :
    A is the date column, B Column is text which can be repeated in same column with another date, C column is another text which can also be repeated with column B,
    Now I need formula if date is less than today and if text in column B and C matches then get cell data of day before yesterday with same text combination.

    Pl. help

    1. Brajesh,

      Sorry, I cannot figure out a formula for this task.

  30. Dear Svetlana,

    I would like requesting help from you, I need in excel Coulmn A2 to A100 each coulmn having different numbers and I need to display in B2 column "More Line" if A2 to A100 coulmn's any numbers greater than 3.

    Lines
    1
    1
    1
    1
    1
    2
    2
    2
    3
    3

    1. Hi Abdul,

      Try the following formula:
      =IF(MAX(A2:A100)>3, "more line", "")

  31. Hi Svetlana
    A2, A3, A4 and A5 cells have the following info
    Lost
    Abandoned
    Won
    Open

    What conditional formatting I must use so that if an OPPORTUNITY_STATE is "Won" than it turns the background green, "Lost" or "Abandoned" it turns red and "Open" it turns blue

    1. Hi Joe,

      Select the cells A2 to A5 and create 3 rules with the following formulas:

      Green: =$A2="won"
      Red: =OR($A2="lost, $A2="abandoned")
      Blue: =$A2="open"

  32. Hi Svetlana, I was hoping to get help on the following:
    If the Average of cells B2 through B14 is greater > then the Average of B9 through B14 , fill in the number of the Average of B2:B14. If not (if the other Average is a higher number) fill in that number in the cell.

    This is how I am doing it and it is not working
    =IF(AVERAGE(B2:B14)>AVERAGE(B9:B14),"=AVERAGE(B2:B14)","=AVERAGE(B9:B14)")

    Thank you

    1. Hi Valeria,

      You were almost there :)

      =IF(AVERAGE(B2:B14)>AVERAGE(B9:B14), AVERAGE(B2:B14), AVERAGE(B9:B14))

  33. Hi All,

    Just wondering if you can help me with below validation?

    B7 text:I have a drop-down here with Yes or No selection (achieved with data validation)

    B8 text: Here is my challenge. If in B7 Yes is selected I would like to mark B8 cell with grey color. If No selected in B7 I would like B8 to remain blank.

    I tried Conditional formatting (=IF($C$7,"No") then no format set but doesn't working.

    All helps, ideas, suggestions are appreciated.

    Elizabeth

    1. Hi Elizabeth,

      IF is not needed in Excel conditional formatting rules because they are conditional per se.

      Try creating 2 rules for B8 with the following formulas:

      Grey: =$B$7="yes"

      No format: =$B$7="no"

  34. Hello,

    I am trying the If statement as follows =IF(C2="delivered", "No", "Yes") but everytime excel returns with: the formula contains an error.

    How can I fix this?

    1. Nevermind solved it

      Thank you anyway.

    2. Hi Sander,

      The formula is correct and works fine in my Excel. Probably you have the List Separator set to ";" in your Windows Regional settings. If so, try replacing commas with semicolons:

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

      1. Yeah, but that was not the problem. It has something to do with the quote signs,these signs do not work "delivered" but if I do the this =""""" the formula correct itself and works fine.

        Not really sure whats going on but it works.

        Thank you for your help Svetlana

        1. Sander,

          This is very strange indeed... Though, sometimes "smart quotes" copied from a web-site may cause a problem and retyping straight quotes in the formula bar fixes it.

      2. Hi,

        I have an attendance chart for my employees. If they have an infraction, it adds a value like +1 or +.5 based on whether they were late or called in sick. If they work an extra day or overtime, it removes a value like -1 or -.5. I use the COUNTIF formula, but the problem I'm having is if they have more OT than call outs, when they call in sick again, it doesn't adjust the 0 balance to +1 (because there are more negative values in the SUM). Can I use the IF function to keep a running total, so that if their value is already at zero it will still add +1 despite there being more negative numbers in the SUM?

        Example: Callout twice in a week (+2), work 3 extra days (-3, but the max is zero as you can't go negative). Call out (+1), the value is still zero, but it should be 1.

  35. Hello Svetlana,

    I am writing this formula in Cell E3 :
    =IF(C4:C9="x",0,15)
    if column c4 to c9 has an "x" or "X" in it it should show zero other wise the value 15. it is showing me #NAME?

    I also tried running :IF(C4&C5&C6&C7&C8&C9="x",0,15). this works for the first cell C4 and the value on E3 changes to zero but as soon as i enter x in c5 or further it shows 15. where am i wrong?
    thank you

    1. Hi Asif,

      Just add the AND statement in the logical test, like this:

      =IF(AND(C4="x", C5="x", C6="X", C7="x", C8="x", C9="x"),0,15)

      1. Svetlana,

        what i was trying to accomplish was that if any of the cells contain "x" it would show a zero. i used your formula and switched the AND with OR and mission accomplished. thank you so much

  36. i want to check more than 2 value what will i do..
    Example, if(A1>100,"3.95",A1>150,"3.25",A1>200,"3.00")

    1. You need a nested IF formula in this case:
      =IF(A1>200, 3, IF(A1>150, 3.25, IF(A1>100, 3.95, "")))

    2. Please provide me that equation.................

  37. Dear Mam,
    if the value of A1 is greater than 200,A1*0.15 Please provide that equation.....

    1. Hi Hari,

      Here you go:
      =IF(A1>200, A1*0.15, "")

      1. thanks

  38. Hi,
    I need formula if date is less than today and text of two columns are matching then get cell data of the same text from corresponding another column.

    Thank You

    1. Hi Brajesh,

      You can use a formula similar to this:

      =IF(AND(A1<TODAY(), B1=C1), D1, "")

      Where A is the date column, B and C are the columns to match, and D is the column to extract the data from.

      1. Hai Svetlana,
        Thanks for help but I need something different as follows :
        A is the date column, B Column is text which can be repeated in same column with another date, C column is another text which can also be repeated with column B,
        Now I need formula if date is less than today and if text in column B and C matches then get cell data of day before yesterday with same text combination.

        Pl. help

  39. Hi,
    What I am trying to do is I need total of A1 and A2 in cell A3 but once it gets to a certain number I need to have some kind of text in cell A3 says your total is over budget
    I have tried this formula but I did not work
    =sum(a1+a2;if(a3>300;"your total is over budget))
    Thank you

    1. Hi Tolga,

      Try this one:
      =IF(A1+A2>300, "your total is over budget", A1+A2)

      1. Thank you, you are the best

  40. Hi I am trying to do a formula where in d3 it has a percentage and e3 has a number. That's the easy part what I'm stuck on, is I want f3 to be a number where if the d3 is under 5% it's 0, 5-10% is 1 and over 10% is 2 and if e3 is 18.5 or less it's 2 18.6-19.9 is 1 and 20 or over is 0. But I need these scores to only show and add up in f4. Is that even possible?

    1. After rereading the page several times I have now been able to figure out the formula myself.

  41. Need a formula for the below.
    If the amount in a another cell is less than $1,500,000.00 the cell should read 60%. If the amount in a another cell equal or greater than $1,500,000.00 up to $2,999,999.00 the cell should read 70%. If the amount in an another cell is greater than and equal to $3,000,000.00 the cell should read 75%. Please help

    1. Hi Diana,

      Here you go:
      =IF(A1<1500000, 60%, IF(A1<=2999999, 70%, 75%))

      For the percentages to get displayed correctly, remember to apply the Percent format to the cell.

  42. can I use it in fill color?

  43. How do I set up a formula to equal 1 if one cell is the same letter as another or 0 if there is no exact match. For example:
    B1= C, C1=C therefore the value is 1

    or IF B1=C and C1=D, the value is 0

  44. Hi can you help me write a formula for multiple if's. I am trying to make the cell say if G18=2 then print 5.00%, if G18=3 then print 10.00%. If G18=4 then print 15.00%. Going up to if G18=10 then print 45.00%. However when I put in multiple if statements it says TRUE if there is a value in the range or FALSE if not. The formula I put in is, =IF(G18=2,"5")=IF(G18=3,"10")=IF(G18=4,"15")=IF(G18=5,"20")=IF(G18=6,"25")=IF(G18=7,"30")=IF(G18=8,"35")=IF(G18=9,"40")=IF(G18=10,"45").
    Please ac==can you help as I cannot find how to do it.

    1. done it don't worry, I did this instead =IF(G14=2,5,IF(G14=3,10,IF(G14=4,15,IF(G14=5,20,IF(G14=6,25,IF(G14=7,30,IF(G14=8,35,IF(G14=9,40,IF(G14=10,45,)))))))))

  45. I have an issue with excel 2010. I have a cell B24 with a formula that produces a number result. I also have cell D13 with a formula that produces a number result as well.

    In a third cell I want the results of B24 and D13 to be compared. Currently, the formula in this third cell is =if(D13=B24,"OK","STOP!!") however it is not working... Please help. :)

    1. Hello Wikelani,

      The formula is absolutely correct and I don't see any reason for it not working. Does it deliver wrong results in your worksheet?

  46. Hello,
    Issue is I need to display certain modified dates dependent on what abbreviation is in block D2 I need to add +30, +60, or +145 to the date in Column M depending on what is in Column D and if D is blank then I need E to be blank also which is where the modified dates will be shown. what I tried is “=IF(D2=AAM,M2+30,””)&IF(D2=ARCOM,M2+60,””)&IF(D2=MSM,M2=145,””)”but that obviously is not working what is the secret formula to make magic happen.

    1. Please use
      =IF(D2="AAM", M2+30, IF(D2="ARCOM",M2+60,IF(D2="MSM",M2+145,"")))

  47. Hallo! First of all, thank you very much for helping so many people!

    I am experienced with Excel, but I am having problem with this: I have two cells (they can contain an error or not). In case of no errors, return the sum of them. If one of them has an error, return the one that hasn't.

    I am doing this below for example, but it´s missing the last part (between " "):

    =WENN(UND(ISTFEHL B1=FALSCH;ISTFEHL B2 =FALSCH);B1+B2;"the one that has no error")

    Yeah, it´s in german. Wenn=if, und=and, istfehl=iferror, falsch=false. At the moment, the error that those two cels can have is "#BEZUG!", but I want the formula to work in case of any errors.

    Thank you beforehand!!

    1. Hi Mia,

      I am not sure I fully understand the required logic, but you may try a formula similar to this:
      =IFERROR(B1+B2; "the one that has no error")

  48. So far, I'm using
    =IF(L3="Regular seating","1","0")
    =IF(L3="Procession/Nominee-Seating","1","0")
    =IF(L3="Other","1","0")

    but I don't know how to tally up the number of each of the replies, which is really what I need to do.
    Can you help? THANKS

  49. Hi I am currently running the formula =NETWORKDAYS(K21,M21,Holidays!A25:A31)-1
    looking to add into that formula IF(K21="N/A","N/A") but I can't seem to get it to sit right with the formula, basically I am looking to make it run the formula if there is a date input into K21 but if N/A is put into K21 I want it to display N/A rather thank the #value error etc? hopefully this makes some sence?

    1. Hi Leigh,

      You can wrap your formula in the IF function in the following way:
      =IF(K21="N/A", "N/A", NETWORKDAYS(K21,M21,Holidays!A25:A31)-1)

      1. Thank you so much, I tried every variation but couldnt for the life of me get it to work! haha @:)

  50. Sorry, my example removed all the blank cells

    1. Hi Jim,

      Try the following formula:
      =IF(AND(A1="x",B1="x",C1="x"),"yes","no")

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