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 45. Total comments: 4557

  1. This formula result: #VALUE!.
    =IF(OR(AND(A4="APPLE",B4="ORANGE"),"DRINK",IF(AND(A4="BREAD",B4="BUTTER"),"EAT")))
    Could you please fix it? The expected result is: DRINK, BUTTER, OR FALSE (either one).
    Thank you in advance.

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

      =IF(AND(A4="APPLE",B4="ORANGE"),"DRINK", IF(AND(A4="BREAD",B4="BUTTER"),"EAT"))

      I hope it’ll be helpful.

      1. Thanks a lot, Alexander.
        It works perfectly!
        Have a nice day.
        Cheers,

  2. Hi,
    Can anyone please help me with my formula.
    I need "Yes" or "No" answer if the number in cell matches a number from any cell in another selected field. These two i have tried so far, but no luck.
    And sorry, my excel is in German.
    =WENN(A13=Tabelle1!A2:A29;"ja";"nein")
    =WENN(B16="SVERWEIS('INT-B-106 Kennzahlen nach Artik'!A13;Tabelle1!A:A;1;Tabelle1!A:A)";"ja";"nein")

    Thanks for help and have a great day!

    1. Hello!
      I propose to use the following formula (in English):

      =IF(SUM(--(IF(A13=Tabelle1!A2:A29,1,0)))>0,"yes","no")

      I hope this will help

  3. I need help in finalizing this formula. =IF(AND(L10>=1,L10<=10),250,SUM(L10-10)*11+250), IF(L10<1,0,""). The formula works great up to the last IF statement. I am needing it to return 0 if L10 is less than one or in other words equal to zero.

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

      =IF(AND(L10>=1,L10<=10),250,IF(L10<1,0,SUM(L10-10)*11+250))

      I hope this will help

  4. Good Morning,
    Please assist, I am struggling with the formula below.
    =IF(Z5>Y5,Y5*30%,IF(Z50,Z5*0%)))
    X Y Z AA
    Tax @25% Tax @25%
    200.00 2,100.00 3,800.00 630.00
    200.00 2,100.00 800.00 240.00
    200.00 2,100.00 3,800.00 630.00
    0.00 2,100.00 -5,400.00 -1,620.00
    200.00 2,100.00 3,800.00 630.00
    0.00 2,100.00 -5,400.00 -1,620.00
    I want Cell AA to be zero if the value in cell Z is less than 1

    1. Hello!
      The numbers in your table do not match the conditions in your formula. Therefore, your calculations are not very clear to me. However, it may be useful to you.

      =IF(Z5>Y5,Y5*30%,IF(Z5<1,Z5*0%))
      =IF(AND(Z5>0,Z5>Y5),Y5*30%,0)

  5. Hi,
    Following is an example of a data sheet with the 4 columns(A, B, C, D).The values in first three columns correspond(match) to each other but in Column D values are randomly located in different rows even though the column is sorted. I need to find out the correct value that corresponds to the value of Start and stop in each row. For example, there is a value in "Position" column that falls between the range of start(3904) and stop(4943) in first row but do not know how to find it?
    Genesymbol Start Stop Position
    Mt-nd2 3904 4943 471
    Mt-atp8 7758 7962 635
    Ppil4 1897338 1929764 645
    Mib1 1970914 2094921 665
    Arrdc1 2129879 2136937 685
    Hspa1a 2699712 2701816 2924
    Glmn 3204390 3247696 2959
    Ncoa2 5616483 5694599 3130
    Slc7a1 8032809 8108973 3140
    Vcpip1 9230984 9257935 3145
    Uimc1 10065947 10130908 3166
    Wasf3 10298555 10391271 3196
    Secisbp2 13555349 13586555 3211
    Fam98a 21051327 21065987 3418
    Samd4a 23665202 23878540 3560
    Gfod1 23823632 23923793 3575
    Npy1r 24779477 24788741 3581
    Hars2 29629184 29638722 3808
    Pcdhb5 30398113 30400696 3860
    Ddx50 32239748 32269146 4055
    Zfp827 32401661 32528966 4252
    Epc2 33641616 33740462 4375
    Tmem132b 35970821 36398247 4600
    AABR07001068.1 37059412 37162590 4708
    Rmnd5b 37073345 37084638 4920
    Fbxo8 37131345 37177034 6331
    Dpysl2 43477629 43542940 7474
    AABR07072400.1 43694183 43694612 7568
    Ugdh 44479614 44502846 7606
    Lias 44507218 44524253 7663
    Tenm3 46731403 46926246 7699
    Senp7 46880928 47027668 7711
    Amph 48304322 48562906 7898
    Pnrc1 48501472 48504512 7925
    Scn2b 49418965 49427690 8025
    Ctc1 55596148 55616890 8040
    Ndst1 55955389 55992886 8115
    Phf23 56605140 56609234 8255
    Tlk1 57013025 57119002 8271
    LOC103690017 57318795 57389902 8308
    Pigo 58461759 58469400 8351
    Mcart1 60944563 60955944 8377
    RGD1305464 61913589 61919875 8394
    Cdh6 63101739 63166510 8441
    Dip2c 63635086 63808465 8483
    Luc7l2 66290389 66354712 8534
    Cyp20a1 67130259 67179693 9260
    Cxxc1 70192493 70197868 9298
    Rasal2 74771522 75059327 9310
    Zswim9 75335894 75354821 9544
    Ppp2r2c 78679214 78902064 9574
    Xrcc5 79659251 79748079 9593
    Irgq 81395841 81399673 9646
    Plcd4 81816872 81844365 9658
    Bcs1l 81868265 81872198 9684
    Limk2 83573928 83641893 9763
    Ick 85413537 85472695 9832
    Pip4k2b 85658324 85684139 9937
    Zmiz2 86652365 86661442 10077
    LOC100360491 86751157 86751793 10125
    AABR07019085.1 87404841 87450320 10298
    Stat5b 88686207 88754830 10334
    Itpkb 98615287 98708150 10790
    Neu4 101284902 101290559 10820
    Dusp10 104284660 104321456 10992
    Prpsap1 105430520 105452229 11834
    Armcx5 106253355 106255059 12021
    Nek9 109124330 109162268 12621
    Mrpl12 109658032 109662536 12762
    Zfp638 115601661 115690273 12786
    Ccdc51 117812099 117830869 12802
    Nup210 122644135 122741111 12858
    Xrcc6 123259761 123280613 12876
    Rtl6 125465117 125465849 12921
    Lurap1 134991997 135001721 12935
    Arid2 137680530 137795656 12948
    Zfp133 138597638 138616132 12975
    Dzank1 138624555 138683319 13006
    Noct 140286661 140306870 13020
    RGD1561327 144445795 144447448 13039
    Ret 150202058 150244373 13099
    AABR07054400.1 152402520 152465229 13336
    Epb41l1 153843206 153893848 13551
    Ing4 157554794 157563353 13580
    Vwf 158088505 158219524 13637
    Oser1 159784128 159802953 13699
    Snx21 161252723 161264819 13826
    Lrp6 168194927 168323752 13916
    Ints13 180855800 180887051 14003
    Tmtc1 182625386 182844292 14149
    Snapin 189878168 189880744 14193
    Wdr3 202447745 202470400 14202
    Rap1a 208193954 208215188 14235
    LOC100911730 214009784 214013766 14706
    LOC100911881 214039481 214074664 14775
    Tspan4 214454090 214473742 14910
    Chid1 214476387 214511530 14970
    LOC108348052 220307394 220314036 14980
    LOC102553088 230476320 230479595 15009
    Alpk1 231996088 232117135 15073
    Jak2 247398598 247458510 15088
    AABR07007121.1 282063929 282064742 15150
    Any help will be appreciated.Thanks!

    1. Hello!
      You want to find the values in the "Position" slider, which are between the Start and Stop values. Between 3904 and 4943 there are values 4055.4252, 4375, 4600.4708 and many others. Explain which one you want to find?

  6. Hello,
    I have the following statement that works.
    =IF(OR(G6 = "high", H6 = "high", I6 = "high", J6 = "high"), 0.3*F6, F6)
    I now want to have the statement to look for a "medium" value in each of those cells and if it is a "medium" it would multiply F6 by 0.6 otherwise it would return F6 unchanged.
    If "high" it would multiply F6 by 0.3
    If there is a "high" in the string then it would ignore any medium.
    Thanks!

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

      =IF(OR(G6 = "high", H6 = "high", I6 = "high", J6 = "high"), 0.3*F6, IF(OR(G6 = "medium", H6 = "medium", I6 = "medium", J6 = "medium"), 0.6*F6, F6))

      I hope this will help

      1. Perfect! thanks!

  7. Hi is someone is able to help
    I need help with an IF formula
    I need help with, if the value in cell A1 sheet1 is + or - 10 of another cell in another sheet say Sheet2 cell A1 if yes then equals Cell B1 in sheet2 .

    hope this make sense thank you in advances

    1. Hello Glenn!
      I hope you have studied the recommendations in the above tutorial. Explain what "value in cell A1 sheet1 is + or – 10 of another cell in another sheet" means?

      1. Hi Alexander,
        I have studied the above tutorial I do understand how to calculate if cell A1 is within + or - 10 of Cell B1, but it is when it comes to the result I don't want it to equal good or bad I want it to if it falls within that + or - 10 I want it to equal another cell C1 but if it doesn't fall within it is a fail.

        1. Hello Glenn!
          If a value is written in cell A1, then the formula can no longer be written to it. This has already been discussed on the forum many times. So your question about the formula in cell A1 does not make sense. Only VBA Macro Can Solve the Problem

          1. Hi Alexander,
            Apologies I should have explained my situation better and included my formula.
            the formula I am running is =IF(ABS(A1-B1)<=C1,"ok","Fail") but instead of if the result is ok it equals a number in cell E1

            1. Hello!
              If I understand your task correctly, the following formula should work for you:
              =IF(ABS(A1-B1)<=C1,E1,"Fail")

  8. Can I find the same for time ???
    I means if my difference in time is greater than 10 mins and my task done within 10mins
    If yes can you explain the formula

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  9. I tried several IF formulas but I am not getting the desired output. Please can you help me provide the appropriate formula to get the output.
    There are the conditions: (if the number is between 1 and 20, the value should be 3)
    1-20 figs - 3 days
    21–100 figs – 5 days
    101–200 figs – 8 days
    201–400 figs – 12 days
    401–600 figs – 14 days
    Please help.

  10. Hello,
    I am trying to understand the correct formula for my project. My goal is to develop an IF statement for the following. If the number in column C is within a range it gets a score of 6-8.

    Score Legend
    1-50,000,000 Scores a 6
    50,000,001 – 100,000,000 Scores a 7
    100,000,001 – 500,000,000 Scores an 8

    Column C Score Column D Score
    400,000,000
    85,000,000
    48,000,000

    I keep getting errors with different formulas. Any help would be greatly appreciated.
    Thank you!!
    Tanner

    1. Hello!
      I hope you carefully read the article above. If I understand your task correctly, the following formula should work for you:

      =IF(AND(A1>1,A1<50000000),6,IF(AND(A1>50000001,A1<100000000),7, IF(AND(A1>100000001,A1<500000000),8,0 )))

  11. Hi there, I'd like some more insight into making this formula work for me. I'm doing a training matrix where I need to know if by today's date someone training has expired or about to expire..

    I have so far:
    =today()

    1. Hello Celina!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

    2. =today() and =today()+30 for expiry in 30 days (between)
      =today()+30 (greater than)
      Would this work?

  12. If income is
    Up to Rs. 7,500 Nil (for male)
    From Rs. 7,500 to Rs. 10,000 Rs. 175 (for male)
    Rs. 10,000 onwards Rs. 200 (for male)
    Up to Rs. 10,000 Nil (for female)

    1. If income is
      Up to Rs. 7,500 then tax will be Nil (for male)
      From Rs. 7,500 to Rs. 10,000, tax will be Rs. 175 (for male)
      Rs. 10,000 onwards, tax will be Rs. 200 (for male)
      Up to Rs. 10,000 tax will be Nil (for female)
      Above Rs.10,000 tax will be Rs.200 (for female)

      1. Hello,
        Below formula should work for you... if the values Sex and Salary are assumed to be in the cells D3 and E3 respectiely...
        =IF(AND(D3="Male",E37501,E310000),200,IF(AND(D3="Female",E310000),200,"")))))

  13. Hi,
    I hope you are well! I just need a quick tip with my formula:
    I need to create a forumla which says IF column F has the word TOR then collumn E must have 100. If it has 100 it displays "correct" if it is less/more than then it displays "incorrect".
    I.E: F=TOR E=100 COLUMN H: CORRECT
    I.E: F=TOR E=90 COLUMN H: INCORRECT

    This is just one part of the formula, how would I then add an additional/multiple rules which also displayed same result.
    For example, if I had the words TOR, TOR1, TOR2, TOR3
    TOR = 100
    TOR1 = 200
    TOR3 = 300
    How can I get column F which contains the word TOR/TOR1/TOR2 etc to match the correct value assigned to it in column E

    I.E: F=TOR E=100 COLUMN H: CORRECT / F=TOR1 E=200 COLUMN H: CORRECT
    I.E: F=TOR E=99 COLUMN H: INCORRECT / F=TOR1 E=199 COLUMN H: INCORRECT
    I hope this explains it well enough. Really appreciate the support!

    1. This is what I have so far but it is constantly displaying "Wrong" in the cell:

      Wrong
      =IF(AND(E9="80.00 UK",F9="TOR",OR(E9="100.00 UK",F9="TOR1",OR(E9="150.00 UK",F9="TOR2"))),"Correct","Wrong")

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

        =IF(OR(AND(F6="TOR",E6=100), AND(F6="TOR1",E6=200), AND(F6="TOR3",E6=300)),"Correct","Wrong")

        Hope this is what you need.

        1. You are an absolute genius! Exactly what I needed!!

          I just need to keep adding the AND section for additional criteria, thank you so much you have saved hours of mine and my colleagues time! Thank you so much again!

  14. Hello,
    Can you help me with a formula which return the value as "Qtr 1, Qtr 2, Qtr 3, Qtr 4' based on the month entered in a cell.
    eg. if Column B as value as Feb, Apr, Jul, Dec in cell B1, B2, B3, B4, in column C the result should be as Qtr 1, Qtr 2, Qtr 3, Qtr 4.

    Thanks

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

      ="Qtr "&ROUNDUP(MONTH(DATEVALUE("1/"&B1&"/2020"))/3,0)

      I hope this will help

  15. This is what I see:

    W Z AG (Results) '=IF(W55000, W5-5000))
    Taxable Amt Non Taxable Amt Over $5000.00 Exempt Amt
    0 $377.50 0
    $2,848.45 $573.90 0
    0 $1,600.35 $(5,000.00)
    0 $12,478.32 $(5,000.00)
    0 $13,864.08 $(5,000.00)
    $9,314.72 $- $4,314.72
    $13,320.09 $- $8,320.09

    When I put your formula in Column it gives the same results:

    Column AG
    Over $5000.00 Exempt Amt
    $(5,000.00)
    $-
    $(5,000.00)
    $(5,000.00)
    $(5,000.00)
    $4,314.72
    $8,320.09

    so not sure why it is ignoring that if column W is zero then Column AG ought to be zero.

    Hope this helps with the explanation.
    Thank you for helping,
    Kathi

    1. Hello Kathi!
      The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.

  16. Attempting to create a formula to show once case record. If it is a zero, then fail, if it a 1, then pass. Data below shows duplicate case records, need to show that if one of the cells to the left of case record is a zero, then the whole case record is a fail, and if they are all ones, then it is a pass. However, don't need the case record to show as a duplicate, only one case record. Thoughts?
    Right the First Time CaseRecordID
    0 28018598
    0 28018598
    1 28018598
    1 28018598
    1 27986560
    1 27986560
    1 27986560
    1 27986560
    1 28008880
    0 28008880
    1 28008880
    1 28008880
    1 28008920
    1 28008920
    1 28008920
    1 28008920
    1 28038434
    1 28038434
    1 28038116
    1 28038116
    1 28038116
    1 28038116
    1 28038434
    1 28038434

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

      =IF(SUMPRODUCT(--($C$1:$C$28=C17), --(IF($B$1:$B$28=0,-9999999999,1))) > 0,"pass","fail")

      After that you can copy this formula down along the column.

  17. Looking to show a result (pass/improve/Fail against a range of numbers in the same formula, how can I do this? Appreciate the help. please see below range;
    0 to 70 = fail
    71 to 89 = improve
    90 to 100 pass

    1. Hello!
      Please try the following formula:

      =IF(A1<70,"fail",IF(A1<90,"improve","pass"))

      I hope this will help

  18. I have a data set and I need to use a nested IF formula.
    Basically, if the word faculty(begins in cell I2) appears, my output should be Dr.
    and if student (begins in cell I2)appears with a sex of M (begins in cell H2), my output should be Mr.
    and if student (begins in cell I2)appears with a sex of F (Begins in cell H2), my output should be Ms.
    To complete the output, First Name and Last Names must also be included EX: Dr. First Last - Mr. First Last - Ms. First Last (First name starts in cell A2 and Last Name B2

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

      =IF(I2="faculty",CONCATENATE("Dr. ",A2," ",B2),IF(I2="student",IF(H2="M",CONCATENATE("Mr. ",A2," ",B2),IF(H2="F",CONCATENATE("Ms. ",A2," ",B2),""))))

      I hope my advice will help you solve your task.

      1. Yes, thank you.

  19. For distance calculation Compare two cell and third must automatic enter .
    If A & B - 2
    If A& C = 3
    If A&D = 4
    If B&C = 5 et., to compare 20 possibilities

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  20. Hello, I am trying to figure out a formula by which if a cell contains yes it will add a specific amount to 4 totals in different cells.

    If yes add 1 million to running Total in 2 different cells, 750k to another and 500k to the last cell.

    What kind of formula would I be looking for?

    Thank you for any help!

  21. Hi there
    I need assistance with the following formula please:
    =IF(AND(K11=0),(OR(ISNUMBER(SEARCH("8000000",C11)),ISNUMBER(SEARCH("9000000",C11)),ISNUMBER(SEARCH("9100000",C11)),ISNUMBER(SEARCH("9500000",C11)))),"NO","YES")

    I need 2 criteria to be met.
    If colomn K's value is = 0
    AND the text in column C contains 8000000 or 9000000 or 9100000 or 9500000
    Result should be NO
    Otherwise YES
    Thanks for your help

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

      =IF(AND(K11=0,OR(ISNUMBER(SEARCH("8000000",C11)), ISNUMBER(SEARCH("9000000",C11,1)),ISNUMBER(SEARCH("9100000",C11,1)), ISNUMBER(SEARCH("9500000",C11,1)))),"NO","YES")

      I hope this will help

  22. I am trying to use excel to add or subtract a value if it falls into a certain ranges otherwise just place the number in the correct box.
    the ranges are:
    145.1-145.5, 146.6-146.999999,147.6-148.0 subtract .6
    146.0-146.4, 147.0-147.4 add.6
    All other just move the entered number the cell.
    I can get it to do it to one set of ranges, but it fails when I try and use multiple ranges.
    Any help would be greatly appreciated.
    Thank you

    1. Hello!
      The formula below will do the trick for you:

      =IF(OR(AND(A1>145.1,A1<145.5),AND(A1>146.6,A1<146.9999),AND(A1>147.6,A1<148)),A1-0.6,IF(OR(AND(A1>146,A1<146.4),AND(A1>147,A1<147.4)),A1+0.6,A1))

      Hope this is what you need.

    2. This is the formula that I am currently trying to use, but numbers outside the ranges are subtracted.
      =IF(OR(B2>=145.09999,B2=146.599999,B2=147.5999999,B2<=148.00001),B2-0.6,B2)

      Example. If I enter 144.100 I still get the answer of 143.500

  23. How do i do an IF formula similar to below that actually works

    =IF(A5>1=(B5*120,IF(A5<1=B5*40)))

    Please help!

  24. I was confused in formula can you please help me out
    1840 P
    1841 A
    1842 P
    1843 P
    1844 P
    1845
    I need to mark "P"(present) and "A"(absent) all numbers but i have only list of numbers which are "P" (present)
    I need to mark both P and A
    will be very thankful if you reply :)

    1. Hello Jamin!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  25. 1.) IF B1=C1 AND D1=TRUE => calculate A1*0.1
    2.) IF B1=C1 AND D1=FALSE => calculate A1*0.2
    3.) IF B1C1 => 0

  26. Anubody can help to solve this?:
    I have following conditions
    Cell A1 = value $1000
    Cell B1 = text A
    Cell C1 = text B
    Cell D1 = cell condition TRUE or FALSE (always on condition is set)

    Now I want to do following in cell E1

    1.) IF B1=C1 AND D1=TRUE => calculate A1*0.1
    2.) IF B1=C1 AND D1=FALSE => calculate A1*0.2
    3.) IF B1C1 => 0

  27. Hi, please I can't make the mega formula for the followings, please help me out:-

    Section Chapters
    I 1 to 5
    II 6 to 14
    III 15
    IV 16 to 24
    V 25 to 27
    VI 28 to 38
    VII 39 to 40
    VIII 41 to 43
    IX 44 to 46
    X 47 to 49
    XI 50 to 63
    XII 64 to 67
    XIII 68 to 70
    XIV 71
    XV 72 to 83
    XVI 84 to 85
    XVII 86 to 89
    XVIII 90 to 92
    XIX 93
    XX 94 to 96
    XXI 97

    For above infomation, how can a cell returns 'Section' in roman letters if fall in the right chapter number?

  28. Hi, I'm trying to figure out how to create a certain formula and am hoping someone can help. Here's what I want to do:

    Ex. If cells F7, F8, and F9 are NOT blank, return a value of "YES" to cell G7.

    Basically, the criteria in F7, F8, and F9 has to be checked off before G7 can be marked off as complete. I can do a "normal" If/then function referencing 1 cell, but am having trouble figuring out how to set a "True" value when referencing multiple cells.

    Thank you!!

    1. Hello Joanne!
      I hope you have studied the recommendations in the above tutorial.
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(F7<>"",F8<>"",F9<>""),"Complete","")

  29. Hi question is there is Incentive which I have to pay to my employees according to their collection please refer below and I would like to calculate their incentive " % " based on their collection. please send me formula for the same.
    Amount Percentage
    25000-30000 10%
    30001-40000 20%
    40001-50000 30%

    Name of the employees Amount collected Incentive
    John 25000
    Paul 32000
    Peter 32500
    Isaac 48010
    Lemuel 32180

  30. 9873424761 6700670610
    9873424761
    9873424761 9873424761
    6700670610
    If first and second column is different then print both
    if first column is blank and second column is number then print number
    if first and second column are same then print any one
    if first column number and second is bland then print first column

  31. Hello,
    Can you please help me how to how to get exact formula of binary computation in excel. if C5 is less than to D5 or D5 is less than C5 and multiply to 20%. Thank you

    1. Hello Johnzin!
      Your conditions "if C5 is less than to D5 or D5 is less than C5" contradict each other. One of them will always be executed. Therefore, your formula does not make sense.

  32. What is the formula

    If below 1,000, the rate is 2.00
    If 1,000 and above, the rate is 2.20

    1. Hello Marvin!
      I hope you have studied the recommendations in the above tutorial.
      =IF(A1<1000, 2 , 2.2)

  33. Hi I'm trying to figure out a if statement for my "total add-on price" column. I want my formula to be if the customer purchased three or more add-on options,(which prices are listed under the different add on options) they receive a 15% discount on all add on options?

    1. Hello Courtney!
      I hope you have studied the recommendations in the above tutorial. Please specify what what formula you used and what problem or error occurred. Include an example of the source data and the result you want to get. It’ll help me understand the problem you faced better and help you.

  34. I'm trying to use the following formula but it's not giving the correct results.

    =IF(OR(AND(J137="ASSEMBLY",K137"N"), OR(J137="ASSEMBLY",K137"Y")),G137,C136)

    What I'm looking for is if J137 = Assembly and K137 is not N or Y display G137, else display C136. I'm not sure what I'm missing here.

    1. Formula should have read
      =IF(OR(AND(J137="ASSEMBLY",K137"N"), OR(J137="ASSEMBLY",K137"Y")),G137,C136)

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

        =IF(AND(J137="ASSEMBLY",OR(K137<>"N",K137<>"Y")),G137,C136)

        I hope it’ll be helpful.

  35. Hi,

    I need to apply two condition in my excel column which IFERROR function (since if divided by 0 the value will return to "-") and second one is IF function (when the divided value become -1 and the value will return to "-").
    very appreciate if you can help me.

    thanks

    1. Hello Saleh!
      Unfortunately, without seeing your data it hard to give you advice.
      I hope you have studied the recommendations in the above tutorial.

      I recommend that you study this article on using the IFERROR function.

      1. Scenario No. 1
        Col.1 Col.2 Col.3
        100 0 x
        Scenario No. 2
        Col.1 Col.2 Col.3
        0 100 x
        Col.3 (x) is to find the percentage diff. between Col.1 and Col.2
        Scenario 1. ((Col.2-Col.1)/Col.1)=-1
        Scenario 2, ((Col.2-Col.1)/Col.1)=Div/0
        so for both scenario i need to return as "-"
        Scenario. 1 Scenario 2
        +IFS(IFERROR((Col.2-Col.1)/Col.1,"-"),"-",(G84-$D84)/$D84=-1,"-")

        Can you advise.

        Thanks
        Saleh

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

          =IFS(ISERROR((B1-A31)/A1),"-", ISERROR((F1-E1)/E1),"-")

          I hope this will help, otherwise please do not hesitate to contact me anytime.

  36. Hi, I'm trying to do an "if" or change an "if" statement. Right now it says
    =IF(W5=5000,W5-5000))
    When figures are put in column Z if there is nothing over 5000 then it puts (5000) in column AG and I want it to read 0. I've tried everything to make it work even reducing the formula to:
    =IF(W5>5000,W5-5000,"0") but it still puts (5000).
    So not sure how to fix this issue just want column AG to read 0 if there is any figure or a zero in column Z.
    Thanks Kathi

    1. the first formula is =IF(W5=5000,W5-5000))

      1. cut and paste is removing some of the formula: =if (W5=5000, W5-5000))
        Hope this works

        1. Hello Kathi!
          I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail.
          You put numbers in column Z, and the formula refers to column W. Is that correct? What does the AG column have to do with this?
          Give an example of the source data and the expected result.
          It’ll help me understand it better and find a solution for you. Thank you.

          1. Hi,
            Column W = Taxable Amount
            Column Z = Non-Taxable Amount
            Column AG = Results
            Example: if Column W has taxable amount less than or equal to 5000 then the result in Column AG reads zero which is correct; however, if Column Z (non-taxable) has an amount and Column W (taxable) has no amount or zero then Column AG (results) puts -5000 and the result should be zero. The only time Column AG would have an actual amount would be if Column W (taxable) is more than 5000 and then any amount over 5000 would be what shows in Column AG (results). It's where I track sales tax for revenue and to get what I report to the state. We have a DOS based accounting system that is very antiquated. I hope this helps with the explanation.
            Kathi

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

              =IF(W1<=5000,0,IF(AND(Z1>0,W1=0),0, W1-5000 ))

  37. Good day, I am trying to write a formula that calculates the accumulation of days for every certain amount of days worked, for example. for every 16 days worked, 1.25 accumulates in the next column.

  38. please coach me on the Nested functions on between and reduction % of the following:
    Between 5,001 and 7,500 - reduction 2.5%
    Between 7,501 and 10,000 - reduction 5%
    Between 10,001 and 20,000 - reduction 10%
    Between 20,001 and 30,000 - reduction 20%
    More than 30,000 - reduction 30%

  39. I Want to put condition like if value of E1 is less than 100 multiply E1 with X1 and if value of E1 is between 101 to 300 multiply E1 with X2. Please help.
    +if(E1<100,100<E1<301),"E1*X1","E1*X2")

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

      =IF(E1<100,E1*X1,IF(E1<301,E1*X2,""))

      I hope it’ll be helpful.

  40. How can I use this condition in an if statement. For example. If CA is between 20 and 25

  41. Hi,

    I am trying to put in a formula that will have 6 awnsers depending on a drop down which has 3 choices and another drop down which is a yes/No drop down. But i cant get it to work im using the IF And fuctions. I you can help it would be much appreciated.

    =IF(AND($P$4="YES",H4="STANDARD"),'Support Item Name'!C2:C8,IF(AND($P$4="YES",H4="INTENSITY 2"),'Support Item Name'!C9:C15,IF(AND($P$4="YES",H4="INTENSITY 3"),'Support Item Name'!C16:C22,IF(AND($P$4="NO",H4="STANDARD"),'Support Item Name'!C23:C29,IF(AND($P$4="NO",H4="INTENSITY 2"),'Support Item Name'!C30:C36,IF(AND($P$4="NO",H4="INTENSITY 3"),'Support Item Name'!C37:C43))))))

    1. Hello Donald!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Describe in detail all the conditions that you use. What result corresponds to each of the conditions? It’ll help me understand it better and find a solution for you. Thank you.

  42. I want to calculate a Reorder Level for Inventory.
    The Formula in text is:
    [Opening Inventory + Material Received - Material Sold = Closing Inventory]
    Closing Inventory cannot be negative and a particular material should be ordered only if
    the Closing Inventory falls below a particular level, say below 5kg.

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

      =IF(Closing Inventory<5,"Reorder",Closing Inventory)

  43. =IF(AND([@[PO Number]]"",[@[Fob Date.]]"",[@[Invoice No.]]"",[@[Onboard Date]]="",[@[BL NO.]]="",[@[Doc''s Sub HSBC]]=""),"NEED GSP SUBMIT",IF(AND([@[PO Number]]"",[@[Fob Date.]]"",[@[Invoice No.]]"",[@[Onboard Date]]"",[@[BL NO.]]"",[@[Doc''s Sub HSBC]]=""),"BANK DOCS PENDING",IF(AND([@[PO Number]]"",[@[Fob Date.]]"",[@[Invoice No.]]"",[@[Onboard Date]]"",[@[BL NO.]]"",[@[Doc''s Sub HSBC]]""),"PROCESS OK","WARNING")))

    Please clarify above formula logic I do not understand

    1. Hello!
      Unfortunately, without seeing your data it hard to give you advice.
      The formula uses named ranges and references to an Excel spreadsheet.
      In many cases, it’s easier to write your own formula than to try to understand someone else’s formula.

  44. Hello, thank you and Please help:

    =IF(R3219="Shopify Payments",(N3219*0.965-0.3), OR(R3219="Stripe Connects",(N3219*0.971-0.3)))

    If shopify payment on Cell R3219, multiple N3219 by 0.965 minus 0.3 cents, but if R3219 is stripe connects, multiple N3219 by 0.971 and minus 0.3 instead.

    Thank you

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

      =IF(R3219="Shopify Payments",N3219*0.965-0.3, IF(R3219="Stripe Connects",N3219*0.971-0.3,0))

      I hope this will help

  45. Hello, I am trying to combine some if statements with index match (Exact Formula Below) and have a mostly working formula except I would like to return a value if there is no value in the returning cell. Basically I am getting "1/0/1900" or "0" when the formula finds an empty cell. I am not sure if the if(len( can be added to the below? If so I am not sure where to place it :)

    =IFERROR(IF($A2="","ADD SERIAL",INDEX('RACS 06-15-2020'!CS:CS,MATCH($A2,'RACS 06-15-2020'!$M:$M,0))),"NOT IN RACS")

    1. Hello Josh!
      Unfortunately, without seeing your data it hard to give you advice.
      Perhaps this IF formula will replace 0 with another value.
      =IF(IFERROR(IF($A2=””,”ADD SERIAL”,INDEX(‘RACS 06-15-2020′!CS:CS, MATCH($A2,’RACS 06-15-2020’!$M:$M,0))),”NOT IN RACS”)<> 0,IFERROR(IF($A2=””,”ADD SERIAL”,INDEX(‘RACS 06-15-2020′!CS:CS, MATCH($A2,’RACS 06-15-2020’!$M:$M,0))),”NOT IN RACS”),"Value")
      I hope it’ll be helpful.

  46. 5. Brittany wants to identify employees who are eligible to take a CPR course at the clubs' expense. Employees who can work as camp counselors are eligible for the course. In cell M3, enter a formula using a nested IF function as follows to determine first if an employee has already been trained in CPR, and if not, whether that employee meets the qualifications to take the course:
    a. If the value in the CPR Trained column is equal to the text "Yes", the formula should display Trained as the text.
    b. Otherwise, the formula should determine if the value in the Camp Counselor column is equal to the text "Yes" and return the text Yes if true and No if false.

    1. Hello!
      Read the article above carefully. It has all the necessary information on using the IF function. You will be able to complete your task.

  47. I need to create a formula that will determine the % Error based on a low and high range for the % of error. In my spreadsheet, I have a table (shown below) that has the % of Error and ranges assigned as follows. If I want to determine the % of error on a cell value that is 29, I need to create a formula that will check all of the potential ranges in the table to find that the right answer is 20%. I have tried to create a formula using IF and AND, but can't quite get it to work. Any help is appreciated!
    % Error Low High
    10% 36 45
    20% 27 35
    40% 18 26
    60% 9 17
    80% 5 8

  48. If cell A value >50000,1% rebate is obtained. If cell A value >100,000,2% rebate , >200,000,2.5% Rebate , >300,000 3% rebate is obtained

  49. I have two columns. If there are duplicates in column A of a certain namex, column B MUST be the same for each duplicate with a true/false as the output. Example
    A1 B1
    123 t2
    123 t2
    I have over 6000 rows and have tried to use two if statements but this entails searching for the duplicates of each name which is tedious as there could be human errors and I could miss one. There is not an equal amount of duplicates for each input in column A either. I also don't know what column B should be. All i know is that they should be equal for the each duplicate of that type in column A. Let me know if you have questions

  50. I want a function that work like this
    I have this options (Invoice, Payment, Credit note, Expense) as a drop-down-list in Cell A1
    entering an Amount on Cell B1 and expect the change in Cell C1 where the balance will change when an option is selected at cell A1
    1. Invoice, the amount in cell B1 will add up to the balance Amount in cell C1
    2. if other options are selected the Amount in B1 will reduce the value in Cell C1

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

      =IF(A1="Invoice",B1+C1,C1-B1)

      The formula can be written in D1

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