Comments on: Excel IF OR function with formula examples

IF is one of the most popular Excel functions and very useful on its own. Combined with the logical functions such as AND, OR, and NOT, the IF function has even more value because it allows you to test multiple conditions in desired combinations. Continue reading →

Comments page 2. Total comments: 158

  1. Hi all,

    Good day! Hoping everyone had a great day! Please i need help on a formula. I want to display the result "ASP" if the starting letter starts with ID, IN, CN etc (whatsoever that I declare as long as it starts with 2 letters) otherwise "NON-ASP" for those 2 starting letters not declared on the formula.

    Samples
    ID0523A2292
    IN623PEA-3666
    TW623PEA-3677
    CN0623MXA-1447
    CN0623MXA-1343
    IN0623MXA-1438
    ID0623MXA-1471
    VN0623A2382
    VN623PEA-3696
    VN623PEA-3697
    VN623PEA-3698
    CN0623MXA-1518AND

    Thank you very mych

    1. Hi! If I understand correctly, extract the first two characters from the text string and perform a math operation on them. The ISERROR function will indicate an error if the value is a letter. Try this formula:

      =IF(AND(ISERROR(--LEFT(A1,1)), ISERROR(--MID(A1,2,1))), "ASP", "NON_ASP")

      1. Hi Alexander,

        The samples I gave were the actual identifications from my work. If the identification starts with ID or CN or VN or TH (many more combinations) then it will display "ASP" otherwise if not declared then it displays "NON-ASP".

        ID0523A2292 - this one should display "ASP" since the 2 starting letter is ID
        CN0623MXA-1447 - this one should display "ASP" since the 2 starting letter is CN
        MX6756VN-123 - this one should display "NON-ASP" since the 2 starting letter is MX

        Thank you,

        1. You would have saved your time and my time if you had described the problem in detail and accurately at once. Use this formula, where H1:H10 is the list of allowed values.

          =IF(ISNUMBER(MATCH(LEFT(A1,2),H1:H10,0)),"ASP","NON_ASP")

  2. Dear Alexander,
    I hope you are doing well,

    I have one question and i need an excel formula for that if possible ,please,
    I have to generate a quarterly report for a real estate company and i have contracts for flats, shops, warehouses etc....
    So what exactly i want is to make sure if the tenant contract is valid during the report period and how many days is the validation?
    For Example,
    The reporting period is from 01.01.2023 Until 31.03.2023.
    The flat contract period is from 22.02.2022 until 21.02.2023.
    So the period i want the excel formula to calculate it is the report period validation during the contract of the flat contract period i mean the period will be from 01.01.2023 Until 21.02.2023 which is "one month which is January+ 21 day from February.
    Could you tell me how can i do excel function that generates the return of "1month +21 days"

    Thank you in advance.

  3. Want to identify cases in 4 categories, values are in number format, however the IF logic is not giving correct answer,
    Expected answer is derived from filter logics at frontend, have created 4 slabs 1 day
    have used 3 different formulas however not able to get desired output, Please help to make corrections

    test = IF(B3<1,"=1,B3=4,B3<24),"4 to 24 Hrs","test")))
    test1 = IF(B3<1,"=1)+(B3=4)+(B3<24),"4 to 24 Hrs","test")))
    test2 =IF(B3<1,"=1 + B3=4 + B3=24,"> 1 Day"))))

    Lead (Hours) Expected Answer test test1 test2
    0.05 < 1 Hour 1 to 4 Hrs <1 Hour <1 Hour
    0.09 < 1 Hour <1 Hour <1 Hour 1 Day <1 Hour <1 Hour 1 Day 1 to 4 Hrs <1 Hour <1 Hour
    1.02 1 - 4 Hours 1 to 4 Hrs <1 Hour <1 Hour
    1.16 1 - 4 Hours 1 to 4 Hrs <1 Hour <1 Hour
    22.68 4 Hr to 1 day 1 to 4 Hrs <1 Hour <1 Hour
    4.48 4 Hr to 1 day 1 to 4 Hrs <1 Hour <1 Hour

    1. used below formula also with OR condition,

      =IF(BE2<1,"=1,BE2=4,BE2<24),"4 to 24 Hrs","test")))
      however not matching with expected output

  4. Hi,

    Having a bit of a problem

    Sheet 1
    1 = 5
    2 = 10
    3 = 15

    Sheet 2
    Enter either 1, 2, 3 and get the corresponding values

    This works when I enter 2. =IF(C11=2,(SoR!F13))
    This doesn't work when I enter 3 but works when I enter 2. =IF(C12=2,(SoR!F13))*OR(C12=3,(SoR!F19))

      1. Thank you for your help Alexander.

        This is my problem. Sheet 1 may have up to 100 entries and I want to select these when I enter the number in a cell in sheet 2

        Sheet 1

        Item Reference Value
        1 10.00
        2 20.00
        3 30.00
        4 40.00
        5 50.00
        6 60.00
        7 70.00
        8 80.00
        9 90.00
        10 100.00
        and more

        Sheet 2 Input Sheet

        Enter any reference from sheet 1 Corresponding Value from sheet 1
        1,2,3,4,5,6,7,8,9,10 10,20,30,40,50,60 etc

        How do I create a formula for 10 or more items

  5. Hello all,

    I appreciate any help.

    I'm looking to create a document that can produce multiple values depending on the parties involved and documentation requested from each. For example.
    If B2 = A then I need X documents
    If B2 = B then I need Y documents
    If B2 = C then I need Z documents
    If B2 = ... the I need ... Documents so on and so forth for about 10 different scenarios. 10 different possible parties with 10 different possible document names.

    Is this possible? I just keep getting "you've entered too many arguments for this function"

    Gratefully - Hoyt

  6. What if you want a function to calculate if you gonna times by 30 if someone is on self catering(C2) of by 95 if the are B&B catering(C3) if you are given number people in cell C7 are 6 people and number of nights they are 7 on D7 and someone is on self catering but you want a formular so that you can calculate those that are on B&B

  7. i have 3 worksheets of part numbers. i want to look up a part number from sheet 1 and see if they exist on sheet 2 and sheet 3, if it does i want it to return a yes. i am a newbie with excel and cannot figure out the formula for this.

  8. Hi Alexander,

    can you help me please.

    i have the following IF/OR formula i'm try to build. Basically its looking up a cell for either ME (Meter),IN (Inch) or CM (Centimeter). if ANY of the dimensions are greater than 1.2 (ME), 120(CM) or 47.2 (IN) there is a value of 70. if not, the value is zero.
    can you see where i'm going wrong on this

    =IF(OR(AP3202="ME",AP3202>1.2,AQ3202>1.2,AR3202>1.2),"70","0"),IF(OR(X3202="CM",AP3202>120,AQ3202>120,AR3202>120),"70","0"),IF(OR(X3202="IN",AP3202>47.2,AQ3202>47.2,AR3202>47.2),"70","0")

    help much appreciated.

    thanks,
    Damien

    1. Hi!
      I do not have your data, so it is difficult to understand your formula and impossible to check it. However, check in the first OR condition: AP3202="ME",AP3202>1.2

  9. Hi there

    Please Suggest I have a few criteria

    There are 3 column A= Inventory on Website, B= Inventory In Hand and C= reserved qty (B-5)

    Need to write a formula where, If A is greater than C then need to correct Website inventory and if C is 0 or less than 5 then update as out of stock and if C is greater than A and greater than 5 then, update inventory

  10. Hi,
    Helpfull article!

    I have 6 variables in total. Only one variable will actually be found each time and then I would like that specific variable back in text.
    *have to use ";" instead of comma's in my excel.
    Doing this now, but not working:

    =IF(OR(ISNUMBER(SEARCH("Var1";A28));"Var1");
    IF(OR(ISNUMBER(SEARCH("Var2";A28));"Var2");
    IF... etc. ))

    1. Hello!
      You can use nested IF formula

      =IF(ISNUMBER(SEARCH("Var1",A28)),"Var1",IF(ISNUMBER(SEARCH("Var2",A28)),"Var2",IF(ISNUMBER(SEARCH("Var3",A28)),"Var3","")))

      If you have many conditions try using the IFS function instead of multiple IF:

      =IFS(ISNUMBER(SEARCH("Var1",A28)),"Var1",ISNUMBER(SEARCH("Var2",A28)),"Var2",ISNUMBER(SEARCH("Var3",A28)),"Var3")

      I hope my advice will help you solve your task.

  11. Hi need help. I would want to automatically get the rates when these combinations are selected. Please see table. Thank you so much in advance.

    Service Paper Size Print Color Rate
    Print - Plain TEXT Letter Grayscale | B/W 5.00
    Print - Plain TEXT A4 Grayscale | B/W 5.00
    Print - Plain TEXT Long / Folio Grayscale | B/W 7.00
    Print - IMAGE (Half page) Letter Grayscale | B/W 7.00
    Print - IMAGE (Half page) A4 Grayscale | B/W 7.00
    Print - IMAGE (Half page) Long / Folio Grayscale | B/W 10.00
    Print - IMAGE (Half page) Letter Colored 12.00
    Print - IMAGE (Half page) A4 Colored 12.00
    Print - IMAGE (Half page) Long / Folio Colored 15.00
    Print - IMAGE (Full page) Letter Grayscale | B/W 10.00
    Print - IMAGE (Full page) A4 Grayscale | B/W 10.00
    Print - IMAGE (Full page) Long / Folio Grayscale | B/W 12.00
    Print - IMAGE (Full page) Letter Colored 15.00
    Print - IMAGE (Full page) A4 Colored 15.00
    Print - IMAGE (Full page) Long / Folio Colored 20.00
    Print - Digital photo 4R Colored 30.00
    Photocopy Letter Grayscale | B/W 5.00
    Photocopy Letter Colored 7.00
    Photocopy A4 Grayscale | B/W 5.00
    Photocopy A4 Colored 7.00
    Scan 10.00
    addt'l - Editing 3.00

      1. Thank you for this but I am not looking for the delimiter. What I am trying to get is the "RATES". For instance if I input "Print - Plain TEXT" on "SERVICE" then "Long / Folio" on the "PAPER SIZE" then "Grayscale" on "Print colour" it will get me automatically the "RATE" of 7.00..

          1. Hey Alexander Trifuntov ! Thank you so much for the help. Works really great! Awesome! Just as the result I really wanted. :D

  12. I am trying to sum a range of cells if another range of cells says either yes or no. If yes then sum the cells, if no, then subtract the amount in that cell. Can someone help?

  13. Hi,

    Can you please help. i need help with the following

    =IF(OR(K20="DE",K20="FR",K20="SE",K20="ES",K20="IE",K20="IT",K20="DK",K20="NL",Z20>150),"GB 320000",K20)

    if for example K20= "CH" is not listed in the above formula. is there an add on to this formula to just show K20 as CH

    Hope this makes sense.

    thanks

    1. this is the full formula i'm looking for, but no joy. help would be greatly appreciated

      =IF($K20="DE",IF($Z20>150,GB 320000),IF($K20="FR",IF($Z20>150,GB 320000),IF($K20="SE",IF($Z20>150,GB 320000),IF($K20="ES",IF($Z20>150,GB 320000),IF($K20="IE",IF($Z20>150,GB 320000),IF($K20="IT",IF($Z20>150,GB 320000),IF($K20="DK",IF($Z20>150,GB 320000),IF($K20="NL",IF($Z20>150,GB 320000),IF($K20="CH",IF($Z20>0,CH)

  14. I am using the following formula, but I am finding examples where the SUM of T to V = 2 in the first argument and it is still returning a Compliant result when it should be Non Compliant for not being = to 3?

    =IF(OR(N46=1,SUM($T46:$V46)=3,N46=0,SUM($T46:$U46)=2),"Compliant","Non Compliant")

    1. Hello!
      You are using the logical OR function. If at least one condition is true, the formula returns TRUE.

      1. Simple formula, but I can't figure out how to use IF, or if it is IF OR or IF AND to nest the ifs.

        Column A (Salary) has values ranging from 10 to 100.
        I want to indicate in Column B whether the numbers in Column A would be, '75 and below,' '50 and below,' and '25 and below.'
        I can do the basic =IF(A2<=100,"100 or less","0") but then get stuck trying to add the '75 and below,' '50 and below,' and '25 and below.'
        Thanks!

  15. Hi,

    Thanks for you great works.

    I am working on a file with column A containing dropdown list of numbers 100, 200, and 300. The number represents "account department", "legal department" and "sales department" respectively.
    How can I make column B dependent on what is chosen on the dropdown list of column A? That is, if 100 is chosen on the dropdown list in column A, I want column B to return "account department" on its own.

    Thank yoy.

  16. I would like to calculate a sum of products, but with a pricing break.

    1st item= $50, 2nd item onwards = $70 each

    Let's say if A buys 3 products, he will have to pay $50 for the first product, for the other 2 items, he will have to pay $70 each.

    How could I create a formula for this problem? Hope you could assist me, it'll be a great help

    Thank you in advance

  17. I'm trying to use IF to show "ok" or "out of balance" if a value is over or under by more than 5%.
    Cell C20 has a value of 700
    Cell C21 has a value of 650
    My formula for D20 is =C20-C21 giving a value of 50
    My formula for D20 is =IF(D20< C20*5%,"Ok","Out of balance")
    This works but if the figures in C20 & C21 were reversed the value in D20 is -50 and shows as"Ok"
    What I want is the formula to show "out of balance" if the value was over OR under by more than 5%

  18. Cell I2="Any Text", J2="Blank Text,K2="Blank Text,L2="Blank Text,
    than need answer in Cell M="Any Text"

    Blank Text = Blank Cell

    one column have any text and other column have no text, I want to type text only automatically

  19. Is there a syntax error with this formula? I'm getting #Name. Likewise with this formula,

    =IF(AND(OR(AH2=”glass”,AH2=”stained glass”,AH2=”window”),I2>14,AA2>20),"Y","N")

  20. I need help,
    I have this scenario where Agent 1 has a ceiling of 500, Agent 2 has 250 and Agent 3 has 150.
    If at anytime any of the agents pay goes above the ceiling, then 10% is calculated on the ceiling if the pay is below the ceiling then the 10% is calculated on that amount

    How do i use IF statement to achieve this in Excel

      1. This works. Thank you

  21. Hello,
    Is there a way to combine two formulas below:
    =IF(B63=TRUE; (G63)-(F63*1,21*D63); 0)
    =IF(B63=TRUE; (G63)-(F63*1,21*D63); 0)

    Tried this way, but it's not working:
    =IF(B63=TRUE; (G63)-(F63*1,21*D63); 0); OR(=IF(B63=TRUE; (G63)-(F63*1,21*D63); 0))

    Thank You for Your time!

    1. Sorry, mistake! I meant:

      =IF(B63=TRUE; (G63)-(F63*1,21*D63); 0)
      =IF(H63="Paid"; (G63)-(F63*1,21*D63); 0

      Tried this way, but it's not working:
      =IF(B63=TRUE; (G63)-(F63*1,21*D63); 0); OR(=IF(H63="Paid"; (G63)-(F63*1,21*D63); 0))

      1. Hi!
        I think you have not read the article very carefully. There is an answer to your question.

        =IF(OR(B63=TRUE;H63=”Paid”);(G63)-(F63*1,21*D63); 0)

        1. I really appreciate Your answer! Thank You!

  22. How To Extract Unique Values or Duplicate Names and sort (A-Z) Based On Criteria In Excel? Using index or match.

    For Example:-

    Sl No# Location Name score
    1 Mumbai Rohit 93
    2 Mumbai Sachin 93
    3 Gujrat Suresh Raina 90
    4 Ranchi M.S Dhoni 85
    5 Ranchi Sorabh Tiwari 85

  23. Your examples helped me find a solution - thanks for posting this page.

    Please check to see if the following is an error in the section "IF OR statement in Excel" where you state the lines below [in brackets like those enclosing this phrase to avoid confusion if I used double quotes]:

    [ Here's is an example of the IF OR formula in the simplest form:

    =IF(OR(B2="delivered", B2="paid"), "Closed", "Open")

    What the formula says is this: If cell B2 contains "delivered" or "cancelled", mark the order as "Closed", otherwise "Open". ]

    However, as I read the formula, it indicates that if cell B2 contains "delivered" or "paid" (not "cancelled") then the order will be marked as "Closed". If you look at the screen shot, the row containing "Cancelled" shows a Status of "Open", not "Closed" as your explanation states it will. Please clarify for your readers.

    1. Hello!

      Of course, it is "paid", not "cancelled". Thank you for pointing that out, fixed!

  24. I am trying to say that if One Cell = this amount add / subtract a Certain amount.

    Can this be done??

    EX: =IF(D6/7=E6,G6) OR (D6/7=E6,H6) OR (D6/7=E6,I6) OR (D6/7=E6,J6) OR (D6/7=E6,K6)

    Thx...Dawn

  25. =IF(AND(A2="VISHAL", B2="HP", C2=610), "6", "10"), IF(AND(A2="VISHAL", B2="HP", C2=2310), "15", "20")

    WILL THIS WORK?????????
    I NEED TO ENTER MULTIPLE RESULT IN A SINGLE CELL, FROM DIFFERENT CONDITIONS.

    1. Hello!
      Your conditions contradict each other. For example, if A2 = ”VISHAL”, B2 = ”HP”, C2 = 900 then the first condition will return 10, and the second - 20.

  26. Hi.. need help.
    i have date today and start date, to calculate the case age but another column is the status of the case, close or open.. so the logic will be.. calculate the case age if the case is still open..

    thank you in advance.

    1. Hello!
      In the condition of the IF function, write down the check that the case is open. If the condition is met, calculate the age using the DATEDIF function.
      Hope you’ll find this information helpful.

    2. I got this formula: =IF(OR(C2="Closed","--"),(SUM(A2-B2)))
      but..
      it's working but the other way around. it calculates the age if the case is marked as "Closed".

  27. Hi, I would like to know a formula to show if something if greater than or less than a number to show a figure for example

    11 years service - if the years service is more than 10 to show 2, if it is less than 10 but more than 5 to show 1 and if it is less than 5 to show 0.

    hope this makes sense.

    TIA

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

      =IF(A1>10,2,IF(A1>5,1,0))

      1. there are some proble with me in excell example
        =if(a1<10,100,"enough",if(d1=0,"niu"))

  28. Please I need your help how can I come up with the formula for this
    45000 =0%
    5000=15%
    Next 2950000=30%
    Excess 3000000=35%

  29. i need a formula like ( date of joinin - current date less than 365 days then the answer should be 0

  30. Hi,
    I am trying to do the following if statements with the last if statement to add on an additional 1 week if P13 = "U" but I can't get this to work. Any help would be welcomed.
    =IF(AND(O131,O133,O135),4,IF(AND(P13="U",2),TRUE)))))

    1. Hi,
      I am trying to do the following if statements with the last if statement to add on an additional 1 week if P13 = "U" but I can't get this to work. Any help would be welcomed.
      =IF(AND(O131,O133,O135),4,IF(AND(P13="U",2),TRUE))))).
      Thanks so much.

  31. How do I write the formula for... If either Cell A1 or Cell D1 contains a term, say "ENGLISH", then the consequent grade of ENGLISH from the C1 or F1 should be filled in cell G1.
    Is it possible?

    1. Hello Michael!
      If I got you right, the formula below will help you with your task:

      =IF(A1="ENGLISH",C1, IF(D1="ENGLISH",F1,""))

      I hope it’ll be helpful.

  32. Can I not make cell to cell comparison with if/or? Here is the formula I am using.
    =IF(OR(D3<E3,D3=E3,"Good", "Review"))

    1. Here is the formula I used after reviewing the responses to other questions on this forum. New formula works.
      =IF(D3<=E3, "Good", "Review")

      1. Hi Sam,
        Your original formula would work as well. You just had to move the other bracket to close off the or( function.
        =IF(OR(D3<E3,D3=E3),"Good", "Review")

  33. Can you help me on the error in this formula. =IF(ISNUMBER($AH15),ANDIF($AH15>150,(" High Random Blood Sugar "&$AH15&" Mg.%. ", "")&" "&IF($AH15>150,"Urine Sugar "&$AI15&". ", ""),("")
    AH15 is Number or Text "ND" i.e. Not Done.
    Thank you.

    1. =IF(OR(ISNUMBER($AH15),$AH15="ND"),IF($AH15>150,"High Random Blood Sugar"&$AH15&"Mg.%.","")&" "&IF($AH15>150,"Urine Sugar"&" "&$AI15&".",""),"")
      The above formula seems to work for me.

  34. I have a price range for warranty coverage. I need to see when sales either sold the item over or under the range for a warranty package. For example:
    Min Product $ Range Max Product $ Range Product $ Sold
    1000 1499.99 269.00
    300 599.99 1049.00
    1000 1499.99 578.00
    600 799.99 1456.00

    I need a formula that tells me if the product sold for $269.00 was "oversold" or "undersold" contract range? I tried =if(or(c1=B2,"oversold"))
    It doesn't work. What am I doing wrong?

    1. A = Min / B = Max / C = Sold
      =IF(C1B1,"OVERSOLD","")

      1. Something is wrong with the formula not being posted properly.
        =IF(C1B1,"OVERSOLD","")
        It should be:
        =IF(C1 less than A1,"UNDERSOLD",IF(C1 greater than B1,"OVERSOLD","")

  35. I need a formula in google spreadsheet that will:
    +1 when the value is >=5,
    +2 when the value is >=10,
    +3 when the value is >=15,
    +4 when the value is >=20,
    +5 when the value is >=25
    The formula I am currently using is:
    =IF(F7>=5,H7+1,IF(F7>=10,H7+2,IF(F7>=15,H7+3,IF(F7>=20,H7+4,H7))))
    This formula is working for the +1 when the value is >=5, but when the value is >=10, it is still adding +1.
    Please Help!

    1. Hello Tina,

      You see, your first condition fits to all other conditions as well - the value is greater than 5. You need to limit each condition and check, for example, if the number is not only greater than or equal to 5 but also less than 10.
      Your formula for spreadsheets should look like this:
      =IF(AND(F7>=5,F7<10), H7+1, IF(AND(F7>=10,F7<15), H7+2, IF(AND(F7>=15,F7<20), H7+3, IF(F7>=20,H7+4, H7))))

      You will find the info about the IF function in Google Sheets in this post.

  36. I have 2 columns, work email(D2) & personal email(E2). I am trying to create a formula in a new field (preferred email) that says if D2 is blank use E2 (if there is a value) or if E2 is blank use D2 or leave blank. Is this possible?

    1. Hi Sue,

      If you love compact formulas, use this one :)

      =IF(D2<>"", D2, IF(E2<>"", E2, ""))

      1. That's awesome.

    2. Hi,
      If I get it right, your task is as follows: if a cell in Column D contains an email address, a formula is to bring it; if not, it should bring an email address from a cell in Column E; if both cells are empty, the formula has to bring nothing. I hope you do not mind lengthy formulas:

      =IFS(OR(AND(N(ISBLANK(D2))=0, N(ISBLANK(E2))=0), AND(N(ISBLANK(D2))=0, N(ISBLANK(E2))=1)), D2, AND(N(ISBLANK(D2))=1, N(ISBLANK(E2))=0), E2, AND(N(ISBLANK(D2))=1, N(ISBLANK(E2))=1), "")

  37. Someone please help me, i cant get this to work

    In column C I enter one of 7 names.
    Depending on the name I want different results in column N
    So
    If the name is
    1 - Andy Black the result should be 400
    2 - Mr Jet, Nina Sven or Mike Young the result should be 600
    3 - Dr Joe, Miss Adams or Neil Foe the result should be 800
    4 - Ms Hard the result should be 1000
    5 - Mr Woo the result should be 1200

    Which formula do I use to solve this?

    1. Hi Björne,
      The following formula suggests itself:

      =IFS(C2="Andy Black", 400, OR(C2="Mr Jet", C2="Nina Sven", C2="Mike Young"), 600, OR(C2="Dr Joe", C2="Miss Adams", C2="Neil Foe"), 800, C2="Ms Hard", 1000, C2="Mr Woo", 1200)

  38. I am looking for the correct formula to use to return the greatest of two values. For example, if Q3 (5.89) is greater than R3 (7.452), I want S3 to show R3 value (7.452). If Q17 (28.86) is greater than R17 (3.105), I want S17 to show Q17 value (28.86).

    1. Hi Jwalker,
      I hope that your task may be expressed in the following way: if the value in Cell R3 is less than the value in Cell Q3, the value from Cell Q3 is needed; if the value in Cell R3 is more than the value in Cell Q3, the value from Cell R3 is needed. If so, here is the formula you could apply:

      =IF(Q3 < R3, R3, Q3)

  39. hi everyone,
    how can i formulate this one?
    if >=6 : full assistance
    if =4 or 5 : half assistance
    if <= 3 : no assistance

    1. Hi Farzaneh,
      I hope the following formula will do the job:

      =IFS(D2<=3, "no assistance", OR(D2=4,D2=5), "half assistance", D2>=6, "full assistance")

  40. So how would I do this? If cell A1 is equal to 10, I want to multiply B1 by ten, but if A1 is equal to 25 I want to multiply B1 by four, but if A1 is equal to 50 I want to multiply B1 by 2.
    Thanks in advance!

    1. Hi Marty,
      I think that both the IF function and the IFS function may help you with your task. Please choose whatever you like:

      =IF(A1=10, B1*10, IF(A1=25, B1*4, IF(A1=50 ,B1*2)))

      =IFS(A1=10, B1*10, A1=25, B1*4, A1=50, B1*2)

      If you feel like exploring the subject, you may find it helpful to read the following articles:
      'Excel Nested IF statement: examples, best practices and alternatives', 'Excel nested IF statement - multiple conditions in a single formula', and 'Use the new Excel IFS function instead of nested IF'.

  41. Hi, Hoping someone can help.
    I'm trying to write a formula using the IF, AND, or OR function but can't get the formula correct. It should be simple, really.
    The conditions are;
    If the SUM of Cells E4:G4 = between 10 and 15, then Cell G14 = 25
    If the SUM of Cells E4:G4 is greater than or equal to 15, then Cell G14 = 50

    Then there's one other result that I'm trying to achieve (in a separate cell but a similar formula)
    If the SUM of Cells E4:P4 = between 400 and 600, then Cell P15 = 10
    If the SUM of Cells E4:P4 is greater than or equal to 600, then Cell P15 = 20
    I can't work out what I'm doing wrong, I wont paste what formulas i currently have to avoid causing a case of mass confusion.
    THANKS IN ADVANCE

    1. =IF(AND(SUM(E4:G4)>10,SUM(E4:G4)15,50,""))

      =IF(AND(SUM(E4:P4)>400,SUM(E4:P4)600,25,""))

      Completely butchered the original answer. This one should work.

    2. =IF(AND(SUM(E4:G4>10),SUM(E4:G4=15,50,"")))

      =IF(AND(SUM(E4:P4>400),SUM(E4:P4=600,20,"")))

  42. I am trying to evaluate if the first date is a weekend or the time is after 5pm.
    Each works on its own but is not working when combines with the OR
    I am getting #NAME?
    =IF(OR(WEEKEND(E2,2)>5,K19>TIME(17,0,0)),"OT", "REG")

    Any Ideas

    1. Apparently I just needed to retype and press the keyboard harder:) it worked the 50th time I typed it I don't know why as it looks exactly the same. But for now I will move on.

  43. Hello,

    Thanks for your teach, but i think that is better to use brackets, especially if B2 can have several values

    =IF(OR(B2={"delivered";"paid"});"Closed"; "")

    instead

    =IF(OR(B2="delivered", B2="paid"), "Closed", "Open")

    Regards

    1. Hi there!

      Hope someone could help me. Trying to figure out my formula. I need to combine if, and & or using text logic. It’s quite simple but my formula can’t capture the correct results. Apparently I use the If and AND and it captures but my ‘’Not Applicable’’ logic won’t work. ?

      Here’s my formula for now

      If(AND(s2=“Cleared”, t2=“Requested”, t2=“Not Applicable”), “Completed”, “Incomplete”)

      FYI I used more than 10 columns. What I’m trying to capture is that some of my values in column has a “NOT APPLICABLE” value.

      Thank you in advance.

    2. =IF(OR(B2={"delivered";"paid"}),"Closed","") correct

    3. Hi Victor,

      I agree, this makes the formula more compact. Thanks for the tip!

  44. Hi there

    please assist
    if A>=2(Fail),if b>=3(Fail),but if A:B>=3(Fail)

    How do i get this into one fomula

    1. So how would I do this? If cell A1 is equal to 10, I want to multiply B1 by ten, but if A1 is equal to 25 I want to multiply B1 by four, but if A1 is equal to 50 I want to multiply B1 by 2.
      Thanks in advance!

      1. Ooops, sorry, didn't mean to reply to your question with my question.

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