Comments on: Excel IF statement with multiple conditions

For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading →

Comments page 21. Total comments: 2544

  1. I am trying to find a relatively simple (I hope) formula for a time sheet.

    Column C is the Start Time

    Column E is the End Time.

    Colmun F would be the total hours worked.

    I have worked out =sum(E3-C3)/100-0.3

    What I need is to equate "OFF" and "LV" to 0 in F3.

    Thanks in advance!

    1. Hi Nicole,

      Sorry, I am not sure I understand the task. Do you enter that formula in F3 and want it to display "OFF" or "LV" when the formula returns 0? Please clarify.

  2. Hi Svetlana

    Could you please help me with the below.

    I would like to have one cell with following two formulas:

    =D43*H20

    But also:

    =IF(E43="","","D43*H20")

    So if E43 is blank then the cell is blank and if it is not blank then it contains the formula D43*H20

    Is this possible?

    Thanks so much

    Luke

    1. Hi Samuel,

      You were almost there :) Just remove the quotation marks enclosing the formula in the last argument:

      =IF(E43="","", D43*H20)

  3. A B (Values) C(Answer)
    MISJ 5000 5000
    DIV 500 500
    MISJ 1500 1500
    JV 1000 0

    Please help me to develop formula for above calculation
    I need " MISJ"and "DIV" values in C column

    1. Hi Nandana,

      Here's the formula for C1:

      =IF(OR(A1="misj", A1="div"), B1, 0)

  4. Hello,
    This formula works: =ROUND(IF($E5="MCP",$V5*Rates!B$3,0),2) and this works: =ROUND(IF($E5="CWA",$V5*Rates!B$4,0),2) but I want them be together in the same formaula...multiple conditions. For some reason, today, I am just drawing a blank and am having trouble combining the two.

    Note: Rates!B$3 = 3% and Rates!B$3 = 4% and $E5 is a dollar amount

    Thank you.

    1. Never mind. I got. :-)

  5. HI,

    I could also use some assistance with another formula. I would like to sum the values from FX:GE to get a total score, but if the total score = 16, I would like it say "-1" instead. Is this possible?

    Thanks!

    1. Hi Courtney,

      Here you go:
      =IF(SUM(FX1:GE100)=16,-1,SUM(FX1:GE100))

  6. Hi Svetlana,

    Thank you for all of your support on this amazing website! I would appreciate your help making one If/then statement for the following:

    If GI = 8, then "-1"
    If GF = 8, then "1"
    If GF + GG = 8, then "2"
    If GH + GI = 1 or 2, then "3"
    If GH + GI = 3 or 4, then "4"
    If GH + GI > 4, then "5"

    Thank you!

    1. Hi Courtney,

      Here's the formula as per your conditions:
      =IF(GI1=8,-1, IF(GF1=8,1, IF(GF1+GG1=8,2, IF(OR(GH1+GI1=1,GH1+GI1=2),3, IF(OR(GH1+GI1=3,GH1+GI1=4),4, IF(GH1+GI1>4,5,""))))))

      Please keep in mind that nested IF formulas elevate the first condition first and if it's met, other conditions are not tested. For example, if GI1=8, the formula will always return -1 regardless of the values in other cells.

  7. Hi, so I'm trying to compare two values but they are not integers. How can I do this?

    Condition: If A1 is "High" and B2 is "Low", C1 should be "Q1". This would be different if A1 is Low and B2 is High, it will print "Q2" and so on. IS this possible?

    1. Hi Ivan,

      Of course this is possible with the following formula:

      =IF(AND(A1="high",B2="low"),"Q1",IF(AND(A1="low",B2="high"),"Q2",""))

  8. I have several false and true statements across a row. I want to make one formula that says if all statements are false, give me false. If one is true, give me true.

    1. Hi Sarah,

      You can use a formula similar to this:
      =IF(COUNTIF(A1:E1, TRUE)>0, TRUE, FALSE)

  9. Hi, I am trying to do an IF function with two text variables. If the other cell says either SLEEVE or FLANGE, then the IF cell returns a 1. I can't figure it out for Excel 2013. Help! :)

    1. Hi Nick,

      What you need is a OR statement like this:

      =IF(OR(A1="SLEEVE", A1="FLANGE"), 1, "")

      1. And what do you add to the formula if it must leave the cell blank is nothing is entered in the column yet? I want the number 2 to be returned if any other word is typed in, but I need the cell to be blank if I did not type something in. Eg
        =IF(OR(A1="SLEEVE",A1="FLANGE"),1,2)

        1. Hi Vinette,

          In this case, nest another If function, like this:

          =IF(OR(A1="SLEEVE",A1="FLANGE"),1, IF(A1<>"",2,""))

  10. Hi

    My data is as below,

    If a cell says something else apart from shipped , then the other cell has to count the due days from today date to an already specified date (say acknowledged date).Which formula suits best for that.

    i.e. If S12 is not equal to "Shipped" then count Today-acknowledged (Q12). the result should be in days.

    Regards
    Rajesh

    1. Hi Rajesh,

      Try this formula:
      =IF(S12<>"shipped", TODAY()- Q12, "")

      1. Worked. GREAT!!! Thank you Svetlana.

        How ican convert the currency value to USD if the available currency is anything else say , in 1 row (A:A) I have AED, Pounds, Euro, RS and they should be converted to USD in another ROW (B).

        1. 1 more point is for the convenience in Row B we can see what currency has hadded. Like A1: 100 B1: Eur and in C1 : I have to have A1 value in USD here. We have standard conversion value.usd to Eur is 1.09 and for AED to USD is 3.67. so n so.

  11. Hi,
    I am looking to input an If function formula that gives me the sum of a range if the range does not contain text or a negative number, and if the range does contain either a text or a negative number, I need it to say Invalid.

    1. Hi Christian,

      You can use a formula similar to this:

      =IF(OR(SUMPRODUCT(--ISTEXT(A2:A6))>0,COUNTIF(A2:A6,"<0")>0),"invalid", SUM(A2:A6))

      1. Thanks, this worked out perfectly!

  12. Hi. I am new to excel so my query is probably straight forward. I have a list of numbers which when added together need to return zero if less than an amount, but the calculated number if it is more. I cannot figure out the second part of the calculation. =IF(D3+D7+V4<1801,0 what comes next to provide a sum more than 1801 when it is. Thank you

    1. Hi Ellie,

      You were almost there :)

      =IF(D3+D7+V4<1801, 0, D3+D7+V4)

      Just pay attention that the formula returns the sum of values in D3, D7 and V4, if it is equal to or greater than 1801.

  13. hi..i am trying to get the result for name whose res1 and res2 is not "N/A"
    Name res1 res2
    back 1 2
    shift N/A 1
    avast 2 1
    now 3 2
    book1 4 1
    apple 5 N/A
    back 2 N/A
    Avast 3 1
    Shift 7 N/A

    1. Hi Alex,

      If you are trying to count names whose res1 and res2 is not "N/A", use this formula:

      =COUNTIFS(B1:B6,"<>"&"n/a", C1:C6,"<>"&"n/a")

      If you are looking for something different, please clarify.

  14. Hi,

    I am trying to make a status condition. Below is are the cells that I want to have a status with. I have 2 columns, Case Status and Bug Status

    Sheet1
    cell# Case Status Bug Status
    G8 Passed
    G9 Failed Fixed
    G10 Failed Pending
    G11 New Bug
    G12 New Bug Fixed

    On the other (Sheet2) I want to know the general status of sheet1, if it is still "Open" or "Closed". My parameters would be, all "Failed" and "New Bug" case status should a corresponding "Fixed" status under Bug status column inorder to have a "Closed" status under sheet2 general status.

    Any help will greatly appreciated. Thank you very much.

    -Jade

    1. I've solved the problem using this statement:

      =IF(COUNTIFS(G8:G12,"=Failed",H8:H12,"Fixed")+COUNTIFS(G8:G12,"=New Bug",H8:H12,"Fixed"),"Open","Closed")

  15. It is exactly working in the same way I want.

    I am really sorry for too many basic questions...I am just trying to learn excel.

    with the above formula, I am getting the output in the way I want..but need a small extension to it.

    Say for example I have this formula in “AA” for SL (sick leave) and “AB” for VL (Vacation leave) “AC” for CF (Compoff)

    Now as per your formula when I give SL it is getting reduced but if I give VL it is not working.

    Also if I enter CF in the default 0 value it should increase the count, for example if enter CF anywhere from A1 to Z1 the count should get increased in “AC”

    1. Subbu,

      I am sorry, not sure that I can follow you. If you can post this question on our forum and attach a sample workbook for better understanding, our support team will try to help.

  16. =IF(OR(A1="A"), 20-COUNTA(A1:F1),"20") this worked well for a single cell.

    Now I want to use the same formula reflecting on multiple cells like:

    Targeted cells that I want the result is in from A1 to Z1 and I applied my formula on AA cell

    Now whenever I enter a value “A” in the cells anywhere from A1 – Z1 the count in the AA cell should get reduced.

    AA = contains my actual leaves per year
    A1-A20 contains the team member names
    A1 to Z1 cells contains the number of days in a month

    If anybody is on leave on that particular day, the count in the AA should get reduced.

    1. Got it :)

      In this case, you need a different formula, like this:

      =IF(COUNTIF(A1:Z1, "A")>0, 20-COUNTA(A1:F1), 20)

      The COUNTIF function counts the number of "A" in cells A1:Z1, and if it's greater than 0 (i.e. if there is at least one "A"), the count will be reduced.

  17. sorry for coming back again...

    But in this formula no matter wheather i give "A" or "B" the count is getting reduced.

    =IF(OR(A1="A", A1="B"), 20-COUNTA(B1:F1), "A")

    i want the count to be reduced only if i give "A"...please help...

    1. Subbu,

      Yes, the formula reduces the count if A1 is filled with either"A" or "B" because that was the requirement: "if the empty cell is filled with “A” or “B” it should reduce the count".

      If you want the count to be reduced only if you input "A" in A1, then remove the OR statement from the logical test:

      =IF(A1="A", 20-COUNTA(B1:F1), "")

  18. it is a very small doubt..

    I am using this formula as below and want to add an “IF clause” to it saying that only if the empty cell is filled with “A” or “B” it should reduce the count...how can I get it...please help

    =20-COUNTA(C5:AF5)

    ignore the above post.....

    1. Assuming that the empty cell is A1, you can use the following formula:

      =IF(OR(A1="A", A1="B"), 20-COUNTA(C5:AF5), "")

      Instead of "" you can supply any value you want the formula to return if A1 is neither A nor B.

  19. it is a very small doubt..

    I am using this formula and want to add an IF to it saying that only if the empty cell is filled with A it should reduce the count...how can i do it...

    1. Hi Subbu,

      It could be something like this:

      =IF(A1="A", value_if_true, value_if_false)

  20. Hi

    Just wondering whether the following can be shortened
    i.e. if B58 shows either TX or SR it will generate
    6% in cell C58

    =IF(B58="TX",6%,IF(B58="SR",6%,))

    Thanks

    1. Hi!

      You can use the OR statement like this:

      =IF(OR(B58="TX", B58="SR"), 6%,)

      1. Thank you so much

  21. Hi, I was wondering if you could help me on this.

    example:

    If A1 and B1 both equal 2, then say yes, elso say no.

    Thanks :)

    1. Nvm, I got it, I was reading your blog and found it, thanks :)

  22. I have also tried the following formula. But no matter if I get a number less than 60 I am still getting a "D" when i should be getting a "C","B","A".
    =IF(E20>100,"F",IF(E20<100,"D",IF(E20<80,"C",IF(E20<60,"B",IF(E20<40,"A")))))

    1. Hi Jessica,

      Because you put E20<100 before others "less than" conditions, Excel checks it first and if the condition is met it simply does not evaluate any other logical tests. And because any value that is less than 40 is definitely less than 100, your formula returns "D".

      As soon as you change the order of IF's, everything works as it should :)

      =IF(E20<40,"A",IF(E20<60,"B",IF(E20<80,"C",IF(E20<100,"D", "F"))))

  23. Hi

    I need to combine the following two IF Statements into one, I am thinking of using the OR function along it

    Function 1
    =IF(Q4="E",IF(T4>=12%,IF(T4<=19.99%,"0.5%",IF(T4<=24.99%,"1%",IF(T430%,"3%",0)))),"0%"))

    Function 2
    =IF(Q4="N", IF(T4>=10%,IF(T4<=19.99%,"1.25%",IF(T4<=24.99%,"1.75%",IF(T430%,"3.75%",0)))),"0%"))

    I tried using the IF OR function but got stuck.

    Any help would be appreciated combining the two functions into one

    1. Some help would be appreciated on this

  24. Please solve it

    I want to put up two conditions.

    Value - 1 :225
    Value - 2 :0.075%
    if ((225*0.075%=0.05,"Actual Value")).

    if the product of the both value is greater then 0.05 then i want the actual value means the product of the both value.
    Is it possible and if possible please explain.

    1. Hi Ankit,

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

      =IF(A1*B1>0.05, A1*B1, "")

      If the product of values in cells A1 and B1 is greater than 0.05, it returns that product, an empty string otherwise.

  25. Hi Svetlana, you're right the IFs are driving me crazy :s
    I can't seem to figure out what I'm doing wrong.

    I have a sheet that contains owner name, product name, number of products.
    I want to create a formula where I calculate the amount of products per owner.

    For example
    A: Pete - B: Apples - C: 40
    A: Steve - B: Lemons - C: 20
    A: Pete - B Apples - C: 30

    Pete is listed under the product "Apple" twice and in total he bought 70 apples. How do I calculate that number?

    I have tried the following:
    =IF((AND(A:A="Pete",B:B="Apples")),C:C)

    What I'm trying to say here is IF column A contains the name Pete AND column B contains the product Apples, THAN all the values in column C that contain the conditions from column A and B should be added up.

    I think I'm close with my formula but I can't seem to figure it out. Could you help me and let me know what I'm doing wrong?

    PS: sorry if I'm posting this multiple times but something seems to be going wrong when I press the send button.

    1. Hi Rachel,

      Excel has special functions SUMIF and SUMIFS to add up values based on one or several conditions, respectively. And they make things really easy :)

      Since you have 2 criteria, the SUMIFS function is the right choice:

      =SUMIFS(C2:C100, A2:A100, "pete", B2:B100, "apples")

      You can find the detailed explanation of the SUMIFS arguments in this tutorial:
      https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/

      PS No worries about the duplicate post, I've deleted it.

  26. Hi Svetlana - i have read through all the posts as best i can. I'm sorry if you've answered this already:

    I have two cells (A1 and B1) that have numbers in them. Sometimes A1 is blank. When there are numbers in both, i want C1 to bring them together with a dash.

    For instance, if A1=3 and B1=4 then C1 should say "3-4".

    If A1=(blank) and B1=4 then C1 should say "4".

    Here is the forumula i'm trying to use:

    =If(A1>0,A1&'-'&B1,B1)

    However, excel does not recognize cell B1 in the formula.

    Thanks!

    1. Sorry - i was using the wrong "quotes" in the formula.

      1. Spot on :) Your formula with double quotes works perfectly!

  27. I am trying to produce a working formula for a sum-if-or statement and can't come up with something that works.
    I have numbers to sum in columns E11-E47. If Column G11-G47 shows a "T" or "S", I would like those numbers correlating in E11-E47 to sum, but if G11-G47 shows an "R" then the value in G11-G47 should result a 0.

    Make sense?

    I have =SUM(IF(OR(G11:G47="S",G11:G47="T"),E11:E47,0)) but it's not working properly and summing all of the values in E11-E47.

    Thanks!

    1. Hi Becky,

      Excel has special functions to conditionally sum cells, SUMIF and SUMIFS. In your case, you can use 2 SUMIF functions to sum values in cells E11:E47 if column G has either "T" or "S" in the same row. And then, add up the results returned by both SUMIF's:

      =SUMIF(G11:G47, "t", E11:E47) + SUMIF(G11:G47, "s", E11:E47)

      You can find more details and more formula examples in the following tutorials:

      SUMIF in Excel - formula examples to conditionally sum cells

      How to use Excel SUMIFS and SUMIF with multiple criteria

  28. I am trying to come up with a formula for the following,
    If H3, I3, M3 or N3>1,"Pass","Fails"
    this works for one cell or any array but not different cells. New to Excel formulas so any help would be appreciated. Thanks!

    1. Hi Michelle,

      Just use the following OR statement:

      =IF(OR(H3>1, I3>1, M3>1, N3>1),"Pass","Fails")

      1. When I write it like this it will show a pass for the column when all are above 1 but when the cell shows a 1 it will not prompt a Fail it only continues to prompt pass. What am I doing wrong?

        1. Will it matter if I have other formulas populated in the cells that are in this formula?

          1. Hi! Can u help me please..
            I would like to enter a value in any cell from a1 to d1 then it will display the same value in cell e1. What is the formula i should write? Thanks

          2. telle me formula

            Excel - If "pass","fail" - How do I set the formula

  29. hey, i'm trying to come up with a function to display Yes if value is more than 22.41 for female athlete OR if it is more than 22.47 for male athlete: if not it should display No.

    1. Hi Dee,

      Asumming that you have values in column A and male/female in column B, you can use the following formula:

      =IF(OR(AND(A2>22.41, B2="female"), AND(A2>22.47, B2="male")),"yes", "no")

  30. Hi,
    Am trying to do the following:
    If G16 >=1 and G16=1, G16<=3),OR(C4="NA")),"", "Low") but it does not work.

    Any help much appreciated.

    1. Hi Stuart,

      You probably mean:
      =IF(OR(AND(G16 >=1, G16<=3), C4="NA"), "", "Low")

  31. Hi! Trying to do the following:

    If G30 is greater than or equal to 100 but less than or equal to 106.99, multiply G25 by 2
    If G30 is greater than or equal to 107 but less than or equal to 118.99, multiple G25 by 3.5
    If G30 is greater than or equal to 119, multiple G25 by 5

    Any help is appreciated!

    1. Hi!

      Here you are:
      =IF(G30>=119, G25*5, IF(G30>=107, G25*3.5, IF(G30>=100, G25*2, "")))

  32. Hi! I am working in excel and need help with an IF formula for the following statements:

    If the cell value is =8 then 30 points are awarded
    If the cell value is >=10 then 60 points are awarded
    If the cell value is >16 then 30 points are awarded
    If the cell value is >17.5 then 0 points are awarded

    This IF statement is meant to calculate whether our wholesaler is stocking the appropriate # of days of inventory. If they stock 8-10 days of inventory they are awarded the maximum points. They are penalized for dipping below or above the targeted range per the schedule above.

    Any help you can provide would be greatly appreciated!

    1. Hi Kristen,

      Here you go:
      =IF(A2>17.5, 0, IF(A2>16, 30, IF(A2>=10, 60, IF(A2>=8, 30, ""))))

      Please note that I use >=8 in the last logical test, because =8 would return nothing for 9 days. Anyway, you can always adjust the conditions the way you want :)

      1. Good Day,
        Can you kindly help me with the following.
        I want to use a function for the following.
        There are codes which starts at 1000-000 till 7400-000, what I want to do is that if a certain code is typed in, there must automatically display 15 or 0 in another column in which I'm typing in the function, 15 if true 0 if false.
        Kind Regards,

      2. Thank you!!!

  33. HI!
    I am working on a spreadsheet to keep track of real estate commissions and need a formula to help me. I thought it was the if function, but it does not seem to be working.

    I need it to do:

    If C3="yes" and D3>500000 then K3= H3*.5 or if C3="yes" and D3<500000 then K3= H3*4

    Any help would be great!

    Thanks,
    Mel

    1. Hi Mel,

      Try this one:

      =IF(AND(C3="yes", D3>500000), H3*0.5, IF(AND(C3="yes", D3<500000), H3*4, ""))

      1. Hi Mam.. i have a doubt in excel
        If column A equals to column c then substract column b and colum d...
        Pls tell me how can i do in excel

  34. Hi,
    I'm hoping someone can help me.
    I have a spreadsheet that I enter X into a cell when a student has completed a task. If they completed four tasks (four X's) the are finished. I'm trying to do a formula that will count the X's for a specific row and if it is 4 or more it will put a "C" in another cell or better yet it will put a "C" and also colour the cell. Is this possible??
    Thanks!!

  35. Hi, I have a question.
    I want a formula to the following:

    If column S2=N return the sum of Q2*G2 or if S2=Y return the sum of R2*G2

    1. Hi SandeeBee,

      Here you go:

      =IF(S2="N", Q2+G2, IF(S2="Y", R2+G2, ""))

      1. Thanks you Sandeebee for asking this question.
        And thank you very much Svetlana for your response.
        You both have really help me solved an issue i have been dealing with here.
        Thank you.

  36. Hi Erin,

    Try the following formula for English scores:
    =IF(AND(C2>D2,C2>39),"pass",IF(D2>39,"pass","fail"))

    And if it works as you expected, make a similar one for Math.

    1. Svetlana,
      That's great! Thank you! Is there a way, perhaps, to join both the english and the math? for example, if english is a passing grade and math is a passing grade, they pass the course. if english is a passing grade but math is a failing grade, they fail the course...? If both grades are passing, they pass, but if one or both grades are failing, they fail the course. Is there a way to put this into one formula, or should I calculate the english separately and the math separately and then create a formula from those?

      1. Erin,

        Of course, there is a way:

        =IF(AND(OR(C2>39, D2>39), OR(E2>37, F2>37)), "pass","fail")

        The formula works with the following logic:
        If either column C (english) or D (english retake) is above 39 AND either E (math) or F (math retake) is above 37, it returns "pass", "fail" otherwise.

        My first suggestion was excessively complicated in fact, and there is a more elegant solution for individual subjects, e.g. =IF(OR(C2>39, D2>39),"pass", "fail")

        1. Thank you so very much! This is MUCH appreciated!

  37. HI, I could really use some help making an if/then equation:

    -If Z2 equals 0, then "4"
    -If Z2 equals 1, and Y2 > 0, then "3"
    -If Z2 equals 1, and Y2 = 0, and U2 > 0, then "2"
    -If Z2 equals 1, and Y2 = 0, and U2 = 0, then "1".

    Thanks!

    1. Hi Courtney,

      You can try the following nested IFs:

      =IF(Z2=0, 4, IF(AND(Z2=1, Y2>0), 3, IF(AND(Z2=1, Y2=0, U2>0), 2, IF(AND(Z2=1, Y2=0, U2=0), 1, ""))))

  38. Hi Svetlana,

    I wondered if you could help me with the following problem!

    I have a spreadsheet which shows expenditure, restricted funds and designated funds.

    If the expenditure is greater than the restricted funds, and the restricted funds fall below zero, I want to use the designated funds instead.

    So if expenditure minus restricted funds is less than zero, use the number in the designated column and subtract the difference.

    I hope that makes sense! Sorry if I'm not explaining it very well! Any help is much appreciated.

    Thanks
    Ciara

    1. Hi Ciara,

      Yep, it makes a perfect sense except exactly what difference to subtract:

      >use the number in the designated column and subtract the difference.

      Also, please specify the column letters for expenditure, restricted funds and designated funds.

      1. Hi Svetlana thanks so much for your help

        Column letters
        Expenditure E
        Restricted F
        Designated G

        Thanks!

        1. Ciara,

          Also, please clarify about the difference:

          >use the number in the designated column and subtract the difference.

          The difference between which numbers shall we subtract?

  39. Hi,

    I need some help creating a formula please. I need to create a formula based on the amount of trucks that passes a certain point. The formula should be based on the following :

    1 - 25 trucks = 50 + that figure
    26 - 49 trucks = 100 - that figure
    50 - 99 trucks that figure
    100+ trucks = 100

    Can anyone please assist?

    1. Hi Juan,

      Supposing that "that figure" means the number of trucks and it is in cell A1, you can use the following formula:

      =IF(A1>=100, 100, IF(A1>49, A1, IF(A1>25, 100-A1, IF(AND(A1>0, A1<26), A1+50, ""))))

  40. i would like crate formula for if time crossed more than 16:00:00 if after 4:00 Pm of an activity using if function the text should say " Activity delayed", " Actvity on time"...

    Please create a form for the same

    MM Rajesh

    1. Hello Rajesh,

      You can do this with the following formula:

      =IF($A1>TIMEVALUE("4:00:00 PM"), "Activity delayed", "Actvity on time")

  41. Hi,

    I hope that this does not confuse anyone and I will attempt to explain what I am trying to do as best as I can.

    I currently have the following formula located in cell P6:

    =IF(O6-TODAY()>0,O6-TODAY(),0)

    This is essentially a countdown cell that stops when the date that appears in cell O6 equals Zero days remaining.

    My question relates to attempting to hide the initial Zero that appears in cell P6 without turning the font white and having to use conditional formatting. I am hopeful that there is a way to write the formula so that cell P6 is blank until a date is entered in O6, then, when the date is entered and the countdown reaches Zero, that Zero remains showing.

    Thanks,

    Dan

    1. Hi Dan,

      Your explanation is perfect :) Hopefully, this is what you are looking for:

      =IF(O6-TODAY()>0, O6-TODAY(), IF(O6<>"", 0, ""))

  42. I would like to create a formula that says if a1*.8>10000, return 10000 and if a1*.8<10000, return the value of a1*.8. Can you help??

    1. Hello Brandy,

      =IF(A1*.8>10000, 10000, A1*.8)

      Please pay attention that the formula will also return a1*.8 if a1*.8=10000

  43. sir i used to manage if condition i excel but according to my new task is to get (in bold ) if the answer of a sum got greater than the required number it should become bold
    ex: the answer we got in a sum is greater then or equal to 100 it should become bold if it is below 100 it should remain as same as it's older font
    any one of you know about this please give me a suggestion

    hoping that it can be resolved as soon as posible

    1. Hello Pavan,

      Select the cell(s) with the sum and create an conditional formatting rule with this formula:
      =$A2>=100 where A2 is the cell with the sum.

      In the "New Formatting rule" window, click the Format button, switch to the Font tab and select Bold under "Format Style".

      1. sorry mam the resolution you have given is not about the subject what i had asked and the subject is for example i'll show you bellow
        23 20 43
        0 46 46
        48 24 72
        15 36 51
        52 25 77
        2 45 47
        216 632 848
        as in the above consider rows indicates the sum of two numbers in the adjacent columns and here the third column indicates the sum or answer and if the answer is greater than the required value it should become bold in the same cell itself with its own value (answer required)

        any one of you know about this please give me a suggestion

        hoping that it can be resolved as soon as posible

        1. Hi Pavan,

          The solution I suggested is exactly for the following case:

          >if the answer is greater than the required value it should become bold in the same cell itself with its own value

          For more information about Excel conditional formatting, please read this tutorial:
          https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-formulas/

          When creating a rule, you simply have to replace A in the formula I suggested with the letter of your SUM column.

          1. thank you mam
            thank you for your suggestion
            i'll be thank full to you

      2. Kindly provide solution to me also

  44. Hi,

    I have cell X6 with a simple calculation of =K6*W6. I am trying to have this same cell calculate =Q6*W6. K6 & Q6 will never have data in them at the same time, so I am just trying to have this cell show the correct result if one of K6 or Q6 has data.

    Any help would be much appreciated!

    Dan

    1. Hi Dan,

      I believe the following formula is what you are looking for:
      =IF(K6<>"", K6*W6, Q6*W6)

      1. Hi Svetlana,

        You're an amazing resource and I thank you for your time!

        This formula worked perfectly, could you tell me how I would hide the Initial Zero value that is located in Cell X6?

        Thanks,

        Dan

        1. Hi Dan,

          Try this one:
          =IF(AND(K6<>"", W6<>""), K6*W6, IF(AND(Q6<>"", W6<>""), Q6*W6, ""))

          It will multiply the specified cells only of both are not empty, otherwise it will return an empty string (blank cell).

          1. Hi Svetlana,

            Thank you!

            Hopefully my final question:

            I have cell AA6 with a formula of =X6-Y6-Z6-U6. Cells Y6 & Z6 do not have a formula associated with them, however, cell X6 has your previous formula =IF(AND(K6"",W6""),K6*W6,IF(AND(Q6"",W6""),Q6*W6,"")) and cell U6 has this formula =IF(AND(K6"",W6"",T6""),K6*W6*T6,IF(AND(Q6"",W6"",T6""),Q6*W6*T6,"")).

            Right now, with no value in any of the cells, cell AA6 shows an error of #VALUE!

            My end goal is to have cell AA6 show a blank cell until any of X6, Y6, Z6 or U6 has a value. X6 will consistently show a value (after it's own formula has been met), but sometimes none of the other cells will show a value and AA6 will simply equal the value in X6. In other instances, sometimes only U6 will have a value, making the end result X6-U6.

            Again, I hope that I have been clear with regards to my question and I appreciate any time that you are able to dedicate to this.

            Thanks,

            Dan

            1. Hi Svetlana,

              I have re-written the formula in cell AA6 to now be this: =IF(X6"",X6-Y6-Z6)-SUM(U6). This looks to work how I want it to, with the exception of hiding the Zero value.

              Thoughts?

              1. Hi Dan,

                If my understanding of your end goal is correct, the following formula should work a treat:

                =IF(X6<>"", X6-Y6-Z6-U6, "")

  45. My formula =CONCATENATE(D20-C20," (",+ROUND(((D20/C20)*100)-100,0),"%)")

    B C D E
    4203 4276 3916 -360 (-8%)

    returns the value -360 (-8%) which is correct. However, I would like the first part of the returned value, in this case -360 to show in brackets and not with a minus sign.

    I don’t know whether I need to nest an IF Function in there, but if so how please?

    Many thanks.

    Tracco

    1. Hi Tracco,

      There is no need for nested IF in this case. You can use the ABS function to return the absolute value of a number, and add another brackets in your CONCATENATE formula:

      =CONCATENATE("(", ABS(D20-C20), ")"," (",+ROUND(((D20/C20)*100)-100,0),"%)")

  46. I have a spreadsheet in which I moderate marks assigned to tests. The if formula I'd like to use should be able to do the following. If say cell C1 is the original mark and B1 is the revised moderated mark, I need excel to give me a mark in A1. If the revised mark in B1 differs more than 5 marks up or down from the orignal it should give me the revised mark if the difference is less than 5 marks it need to give me the original mark. I have entered this formula { =IF(C39=5>C40;C39; IF(C39=5<C40;C39;C40)) and also tried this one =IF((OR(G40=5G39));G39;G40) } but Excel doesn't seem to give me the false value when it should be false and only gives me the true values each time. What formula should I use or how should I change the present one I have?

    Thank you.

    1. Just to clarify it more, I changed the cell values in this reply.

      I have a spreadsheet in which I moderate marks assigned to tests. The if formula I'd like to use should be able to do the following. If say cell C1 is the original mark and B1 is the revised moderated mark, I need excel to give me a mark in A1. If the revised mark in B1 differs more than 5 marks up or down from the orignal it should give me the revised mark if the difference is less than 5 marks it need to give me the original mark. I have entered this formula { =IF(C2=5>C3;C2; IF(C2=5C2;C3=5<C2));C2;C3) } but Excel doesn't seem to give me the false value when it should be false and only gives me the true values each time. What formula should I use or how should I change the present one I have?

  47. Hi
    I would like to set up a single cell to display the highest value in a column
    Any advice would be much appreciated thank you.

    1. Hi Jackie,

      You can use the MAX function, for example =MAX(A:A)
      If a cell with the formula is in the same column, then you need to specify a range, e.g. =MAX(A1:A100)

  48. Hello....In one column i have 3 text values, how can i get the total of these 3 values

    1. Hi Arshi,

      I am not sure what exactly the total of 3 text values is. If, for example you have the following values: A1="a", A2="b" and A3="c", you can concatenate them into "abc" by using the concatenation operator =A1&A2&A3

      If you are looking for something different, please clarify.

  49. need help with multiple arguments and conditional formatting
    I have 03 fields (Cell 1, 2 & 3) (data validated) with a list.
    I want this Cel1 3 to be highlighted in red

    if Cell 1 is "None" and
    Cell 2 is "Non Staff" and
    Cell 3 is ">12 Months"

    The I want Cell 3 to be highlighted in Red

    1. You don't need the IF function in Excel conditional formatting. Just use a usual AND as follows:
      =AND($A$1="None", $A$2="Non Staff", $A$3=">12 Months")

  50. Hi,
    I need a help in excel. Suppose in column A, user will be entering the date and in B column month will get updated automatically. formula would be in B column. Condition If cell in column A is blank, then it will return "Text", else it will return the month of the date entered in A column.

    Earliest reply would be highly appreciated..

    1. Hi!

      Try this one:

      =IF(A1="", "text", MONTH(A1))

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