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 122. Total comments: 4573

  1. IF
    functions
    which
    returns
    NO
    NEED
    or
    SEND
    NOW
    or
    CHECK
    IT
    IN
    TWO
    WEEKS

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

  3. I have sets of data, for example like this
    Sam Bella Sara Adam
    Modal $1,000 $2,000 $3,000 $1,000
    Work Order Cost
    Sam, Adam $100
    Bella, Sara, Adam $200

    I need to divide the value of 'cost' to the respective individu. For example, 'cost' $100. I want this value divide evenly to Sam and Adam based on their respective modal value . So the calculation supposedly, ($100x$1000)/($1000+$1000) = $50. Same goes with Adam since their modal value is same.

    So I want a formula which can capture the names and return the value needed in the calculation.

    Please assist me to solve this. Thanks.

  4. if A1=blanck (no value) then B2 (where the curser is)should be blank
    if A1 is "not like" "ES/*" then B2 NM
    Pl let me know the formula

  5. In excel file I have 5 coloums with different name.

    In another excel file I have 100 rows with 2 coloums.

    In one coloumn there is amount and another coloumn there is names which is there in 1st excel file

    please suggest a formula through which I can sum the amount from 2nd excel sheet to 1st excel sheet according to the name.

  6. This was incredibly helpful. Excellent article. Thank you!

  7. Dear Svetlana,

    I am also trying to learn some of the insides. Please help with the following: let's say I have 10 products with a cost of 1,2,3,4,5..10Eur. In order to find a sale price I want to put them in categories: from 1-4,5-7,8-10. Each category has a different percentage added: 1-4: 200%; 5-7: 150%; and 8-10: 100%.
    How can I link a formula to automatically multiply the purchase cost with the right percentage?
    Thank you!

  8. Hi,
    I am trying to write formula for the following condition in my work sheet but I am unable to write can I get help?

    condition : first condition if(c2=1) and count(P2:R2)=0,then "OD", "OT" ) or if(c2=2) and count (M2:R2)=0, then"OD", "OT" )

  9. Hi,
    I'm looking for a formula that could be used like this:

    Year number : 1 - 30
    Tables x, y, z the number of purchases made in table x which is in euros, the number of purchases made in table y in francs, and the number of purchases made in dollars.

    If I chose year 6 it will give me a value in tables x. THEN IF I wish to chose year 19 it will give me another value in table x. How do I make this formula as I write in the number? Thanks!

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

  11. Hi there

    What formula do i use to get correct result for:-
    if Cell A2 contains "ABC" and B2 cell contains "100%" then i want C2 cell shows "OK" & if Cell A2 contains "DEF" and B2 cell contains ">=50" then i want C2 cell shows "OK" & if Cell A2 contains "XYZ" and B2 cell contains ">=35%" then i want C2 cell shows "OK" otherwise C2 shows "Pending"
    Like below:-
    Pocedure Result Process
    ABC 100% OK
    DEF 50% OK
    XYZ 35% OK
    ABC 90% Pending
    XYZ 35% OK
    DEF 40% Pending
    ABC 75% Pending
    XYZ 10% Pending

  12. Very informative Site: i am having a problem please solve it out, Thanks.

    I want Result in a certain cell: if Value in E3 is above 400000 but less than 750000, then (E3-400000) x 5% and add zero, but if value is greator than 750000 but less than 1400000, then (E3-750000) x 10%+17500, i am unable to figure it out,
    Please help

    I am using this formula. IF((AND(E3>=400000,E3=750000,E3<1400000),(E3-750000)*10%+17500),0)

  13. Hi,

    What formula can I use to assign if the particular time is of AM or PM shift? Here's the scenario:

    AM shift falls within 8:00 AM to 19:59 PM
    PM shift falls within 20:00 PM to 7:59 AM

    However, cell value is in date and time, while above are of time value only.

    Thanks!

  14. =IF(OR(ISNUMBER(SEARCH("Top 25%",E9)),ISNUMBER(SEARCH("Top 50%",E9))*ISNUMBER(SEARCH("Bottom 50%",E9))),"12","8")

    In the above formula I want "Bottom 50%" to be 4. if I am adding 4 after eight in formula. I get error .

    Please help

  15. Can someone assist me. I have the following criteria I have a range of cells from F4:F14 where some cells have text and the others have numbers.
    What I need to accomplish:

    Pass if cell value F4 is 0.1 or higher
    Pass if cell value F4 has any text
    Fail if cell value F4 is equal to 0
    then I need to display (Pass / Fail) in cell F15 which is the total of F4:F14

  16. Hello
    Could you please help me with the following formula:
    If a cell (date of booking) is blank then 0 or if the cell ( date of booking )> another cell (date of enquiry) then also 0 otherwise 1.
    Thank you

  17. Hi,

    I want to report a result =0.05 in one digit. Is it possible with 'IF' condition.

    Regards
    Kumar swamy

  18. if, 1)for 0 to 250000 ,tax is 0%
    2)for 250000 to 500000 tax is 10%
    3)for 500000 to 1000000 tax is20%
    for 1000000 to above tax 30%
    e.g.,if taxable income is 650000 then tax will be
    for 1) for 250000x0%=0
    2) for 250000x10%= 25000
    3) for 150000x20%=30000
    so, total tax = 55000
    what will be the formula

  19. Hi Svetlana,
    I hope you can help me I really need your assistance on this. I am working on a time sheet where I am trying to execute the following:
    (time out-time in)-meal break time. The output will go into the column "regular time". That is no problem and easily accomplished. The hitch is I want the output to go to the column "vacation time" if the block in the column "Vacation day" has a Y in in it. To summarize I am trying to get:
    "(Time out-Time in)-Meal break=regular time, if Vacation day="Y" then (Time out-Time in)-Meal break=Vacation time."
    I hope this makes sense and I really appreciate the assistance!!
    Thank you in advance for the help!!
    Kind regards,

    Raymond

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

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

  22. 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",""))

  23. Good afternoon,
    I am working on a time sheet where I am trying to execute the following:
    (time out-time in)-meal break time. The output will go into the column "regular time". That is no problem and easily accomplished. The hitch is I want the out to go to the column "vacation time" if the block in the column "Vacation day" has a Y in in it. To summarize I am trying to get:
    "(Time out-Time in)-Meal break=regular time, if Vacation day="Y" then (Time out-Time in)-Meal break=Vacation time."
    I hope this makes sense and I really appreciate the assistance!!
    Thank you in advance for the help!!
    Kind regards,

    Raymond

  24. Svetlana,

    Hi! I have a spreadsheet that I am using to calculate a certain ratio. A lot of times it will come back with a null value which is what I want. Formula is: =IFERROR(AR416/H416,"")

    But, there are times where the value comes to $0.00 which unfortunately gets pulled into my AVERAGEIFS formula and pulls down the average considerably.

    So I tried this formula to counteract that:
    =IF(AR419/H419>0,IFERROR(AR419/H419,""),"")

    But now I get the divide by zero error in a few cells: #DIV/0!.

    Is there a way to use an IF statement to return a null value for everything that isn't greater than 0?

    Thanks so much!
    Shawn

  25. Perfect! Thanks so much for your help Svetlana!

  26. Hi WENDY,

    Try this one:

    =IF(AND(R1126>10.1,S1126>2.1),"TF2", IF(R1126>=10.1,"TF1", IF(S1126>=6.1, 3, IF(S1126>=2.1, 2,IF(S1126<=2,1)))))

  27. Hi! I am trying to add one more equation to my IF function and am not sure what to do. Here is my equation so far: =IF(R1126>=10.1,"TF1",IF(S1126>=6.1,"3",IF(S1126>=2.1,"2",IF(S1126<=2,"1")))) What I also need it to do is come back with a posting of TF2 if R1126 is greater than 10.1 AND S1126 is greater that 2.1. I cannot figure out what I am doing wrong. Any suggestions?

  28. Hi

    Can you help me with why this function is giving invalid errors? My error starts at this statement:IF(B43="",IF(A43=TODAY(),("OTW"),("CHECK ETA"))

    and again at this statement:IF(G43="",("IN ROUTE"),IF(L43"",("DELIVERED"),IF(H43="",("NOT AVAIL"),IF(I43"",("SCHEDULED"),("NEEDS SCHEDULED")))

    =IF(F43="",IF(A43>=TODAY(),("OTW"),IF(B43="",("CHECK ETA"),("ARRIVED")),IF(B43="",IF(A43=TODAY(),("OTW"),("CHECK ETA")),IF(D43="",IF(B43"",("AT PORT"),("OTW")),IF(E43="",IF(D43"",("INGATED"),IF(B43"",("AT THE PORT"),("OTW")),IF(G43="",("IN ROUTE"),IF(L43"",("DELIVERED"),IF(H43="",("NOT AVAIL"),IF(I43"",("SCHEDULED"),("NEEDS SCHEDULED")))

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

    Regards
    Rajesh

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

  31. i have a query on the below data...

    i have max value and min sale value..
    i have value for march sale.
    if my sale is gone beyond max value upto 25%, then should be zero, or between the max value then it should be show some points based upon or will calculate in 10.
    The same is required for min value too...but i want to use both condition in one single column.

    Pls help.quite urgent for me.

  32. 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,""))

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

  34. I want to use if function for below details. Kindly assist:

    Slab Commission
    50000 200
    75000 500
    100000 700
    125000 900
    150000 1100

    I want to mark commission upon completing mentioned sales slabs, getting difficulty on doing so. your assistance required.

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

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

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

  38. I have a cell that I am importing Numbers or Words into.

    I attempted to use the following equation, which works but I was trying to get it to display an equation in the Number format.

    =IF(ISTEXT(M17), "Did Not Play", IF(ISNUMBER(M17), "Number", IF(ISBLANK(M17), "Did Not Play", "")))

    =IF(ISTEXT(M17), "Did Not Play", IF(ISNUMBER(M17), "=M17-36", IF(ISBLANK(M17), "Did Not Play", "")))

    The equation above does work, but it doesnt display the solution to the equation M17-36, it just displays the words.

    Any help is appreciated.

  39. Hi!

    I have written a formula as below.... it should give a result if I go & check manually in the data for an example, but it is showing as FALSE... please help....

    =(IF(Education_Experience_Matrix!G:G=Table134[[#This Row],[Column2]],Education_Experience_Matrix!F:F,IF(Education_Experience_Matrix!I:I=Table134[[#This Row],[Column2]],Education_Experience_Matrix!H:H,IF(Education_Experience_Matrix!K:K=Table134[[#This Row],[Column2]],Education_Experience_Matrix!J:J,IF(Education_Experience_Matrix!M:M=Table134[[#This Row],[Column2]],Education_Experience_Matrix!L:L,IF(Education_Experience_Matrix!O:O=Table134[[#This Row],[Column2]],Education_Experience_Matrix!N:N,IF(Education_Experience_Matrix!Q:Q=Table134[[#This Row],[Column2]],Education_Experience_Matrix!P:P,IF(Education_Experience_Matrix!S:S=Table134[[#This Row],[Column2]],Education_Experience_Matrix!R:R,IF(Education_Experience_Matrix!U:U=Table134[[#This Row],[Column2]],Education_Experience_Matrix!T:T,IF(Education_Experience_Matrix!W:W=Table134[[#This Row],[Column2]],Education_Experience_Matrix!V:V,IF(Education_Experience_Matrix!Y:Y=Table134[[#This Row],[Column2]],Education_Experience_Matrix!X:X,IF(Education_Experience_Matrix!AA:AA=Table134[[#This Row],[Column2]],Education_Experience_Matrix!Z:Z,IF(Education_Experience_Matrix!AC:AC=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AB:AB,IF(Education_Experience_Matrix!AE:AE=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AD:AD,IF(Education_Experience_Matrix!AG:AG=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AF:AF,IF(Education_Experience_Matrix!AI:AI=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AH:AH,IF(Education_Experience_Matrix!AK:AK=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AJ:AJ,IF(Education_Experience_Matrix!AM:AM=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AL:AL,IF(Education_Experience_Matrix!AO:AO=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AN:AN,IF(Education_Experience_Matrix!AQ:AQ=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AP:AP))))))))))))))))))))

  40. 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")

  41. I am working on the calculations of moon on excel,,I have download spreadsheets but they use macros,,,can you plz tell me that how can I extract real formulae from the codes???

  42. I need to find the exact data for using index and indirect formula

    A 50
    a 40
    B 30
    b 20

    Using Index and match funtion could you help me

    =MATCH(F$39,INDIRECT("'"&$A$1&"'!A"&F$41&":IV"&F$41&""),0)
    getting Error (#Name?)

    =INDEX(INDIRECT("'"&$A$1&"'!A"&$F$41&":Z"&$F$42&""),MATCH(F$39,TRUE,EXACT(INDIRECT("'"&$A$1&"'!"&F$41&":IV"&F$41&""),0)))
    Ref error

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

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

  45. It is working for one cell that is A1. What if i want to apply the same formula from A1:Z1.

    I tried but it is giving an error

    please help

    1. Do you want to copy the formula to other cells? If so, you will need to use absolute or mixed cell references depending on your data structure. To be able to advise something more concrete, I need to know the exact formula you are using and what exactly result you expect it to return in other cells.

  46. 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), "")

  47. It worked well thanks again

  48. That was a very speedy reply.. Your are great…appreciate it and thanks you very much for your help.

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

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

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)