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 12. Total comments: 2538

  1. I have 3 cells that have yes or no drop down boxes. How can I write a formula for a 4th cell that gives a different specific number value if all are yes, all are no, one is yes and two are no, two are yes and one is no, and all other possible combinations?

    1. Hello!
      To count the number of "Yes" in cells, you can use the formula

      =SUM(--(A1:D1="Yes"))

      I cannot give more detailed recommendations, since the description of your problem is very short.

      1. I think this is what he wants Alex (hopefully lol):
        Col A Col B Col C Col D
        Row 1 Answer 1 Answer 2 Answer 3 Count
        Row 2 Yes Yes Yes 3
        In Cell D2 Type this in: =COUNTIFS(A2:C2, "Yes")
        and as Long as your Yes is exactly the same as the yes in the formula you should be fine

  2. Hello, This has all been really helpful and i've solved an awful lot of issues with it. Unfortunately I have one I cant solve.
    I'm trying to reduce a list of items that have qtys in the next column. The list may be 20 rows long but only 10 have qtys. I would like the new list to ignore blank rows and only pull through the 10 items into a new list.
    Column A Column B
    Books 5
    Films
    CDs 56
    Tapes 4
    Videos

    The new list should read
    Books
    CDs
    Tapes

    Without blank rows between them, I've been playing with nested IF & VLOOKUP but cant get the blanks lines removed.I've started with a simple IF formula below
    =IF(B1="","",A1)
    I feel like the 'If True' part needs to have something that tells it to look for the next non blank cell.

    1. Hello Alan!
      Select the range of cells in which you want to get the result. Then, in the formula bar, enter your array formula.

      =IFERROR(INDEX(A1:A10, SMALL(IF(ISNUMBER(B1:B10), ROW(A1:A10)-0), ROW(INDIRECT("1:"&ROWS(A1:A10))))),"")

      This is an array formula and it needs to be entered via Ctrl + Shift + Enter.
      I hope my advice will help you solve your task.

      1. Well, it appears to be doing mostly what I'm looking for but has pulled the wrong items through. Does it matter if the number in question is 0.7, instead of a whole number?

        I can't see how it has pulled the items through given the selection I have gone with as it would skip them as they are blank. So unsure where I may have selected the wrong details.

        1. Hello!
          The formula I sent to you was created based on the description you provided in your first request. The formula works with any numbers. But if your number is written as text, then it won't work. You haven't written what exactly doesn't work. But I recommend checking your data. You haven't written what doesn't work. And I will not guess.

  3. I am trying to have one cell reference another, but if blank have it reference a different cell with getting a FALSE response.
    I have been using
    =IF(L4="",D4)

    and this works as long as L4 remains blank, but the goal is if I put something in L4 then THAT is what goes into my desired formula cell, not "FALSE."

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

      =IF(L4="",D4,L4)

      I hope my advice will help you solve your task.

    2. Sorry, *without getting a "FALSE" response.

  4. Hello! I need help with an IF formula:

    If column A is CAD, return value from column F
    If column A is USD, return value from column F & G with a space between the two values. Column F will always be text and G will be numbers.

    Thanks!

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

      =IF(A1="CAD",F1,IF(A1="USD",F1&" "&G1,""))

      or

      =IF(A1="CAD",F1,IF(A1="USD",CONCATENATE(F1," ",G1),""))

      or

      =IFERROR(IFS(A1="CAD",F1,A1="USD",CONCATENATE(F1," ",G1)),"") (Office2019, Office365)

      I hope it’ll be helpful.

      1. Thank you sooo much this worked perfectly!!

  5. Hi, I am new to excel formulas. Can you help me with this
    if a date listed in a column of another page equals the same date of the current page, then a specific column/row total from the other page equals total on current page designated column/row.
    thanks

    1. hmm, guess I don't even get a reply...? thanks

  6. I have days in one column line 1, 2, 5, 6, 1, 2, 6 5,3,2,0,4,6,7,4,1,3,2
    I need the result like if the days are between 1 to 3, then the next column should update as 1 to 3 Days.

    1 next to that cell should show 1 to 3 Days
    2 next to that cell should show 1 to 3 Days
    3 next to that cell should show 1 to 3 Days
    4 next to that cell should show 4 to 6 Days

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

      =IF(A1<4,"1 to 3 Days",IF(A1<6,"4 to 6 Days","7 Days" ))

      I hope this will help

    2. Hi,
      had to try. I have a suggestion for this. It is not pretty, someone for sure will find more elegant solution, but it should work.

      For me in this forum the larger than and smaller than symbols doesn't seem to show at all so I'll use capital L for larger than symbol and capital S for smaller than symbol. Let's assume that the list of days is located to A-column and it starts from line 2. I wrote this formula on cell B2.

      =If(A2L6, "error, too high date", IF(A2L=, "4 to 6 Days", IF(A2=0, "error too low", IF(A2S=3, "1 to 3 Days", "unknown error")

      Hopefully this helps :)
      Antti

  7. Hi,
    I think this answer should be exctremely easy, but I just cant find the correct answer.

    I have column with differend numbers (1-18 representing differend areas and I have named the column "Area") and I like to use the Area column to let my formula count all the data from that spesific line. Formula looks something like this:

    =AVERAGEIFS(E3:E1500, E3:E1500,"0", Area, 1)
    (there might be errors in my typing, because Finnish version has a bit differend style to write the formula).

    I would just like to be able to choose with the same time with the same formula multiple area numbers. Like:
    =AVERAGEIFS(E3:E1500, E3:E1500,"0", Area, 1 or 3 or 8)

    Thank you for your help.

    1. I don't know why it removed my typing for with the 0. The formula checking that the counted number is differend than 0 (with Finnish Excel it is " 0")

      1. Ok, larger than and smaller than wont show up :)

  8. hey everyone, need help with a formula with a different outcome for 4 different Conditions;
    IF A1=R output should be 25
    IF A1=RR output should be 37.5
    IF A1=G output Should be 50
    IF A1=GG output Should be 93
    please anyone???

    1. Hi,
      Try this.
      =IF(A1="R", 25, IF(A1="RR", 37.5, IF(A1="G, 50, IF(A1="GG", 93, "Wrong input"))))
      Sincerely,
      Antti

  9. Hi, I am using the IF with AND & OR functions. Within that though, I have a cell with a flavour name, which I want to assign a number. How do I do that, please?

    1. Basically, I want to say "(C3)banana + (D3)1.0 = (E3)$40" or "(C3)banana + (D3)2.0 = (E3)$75"
      C3 being the flavour and D3 being the weight, while E3 has the function which gives the price for the stated flavour and weight.

  10. Please send me correct formula

    BELOW 1 THEN 0
    FROM 1 TO 250000 THEN ACTUAL FIGURE
    FROM 250000 ABOVE THEN 250000

    1. Hi,
      Again, I don't know why, but for me this message board wont show my larger (L) than and smaller (S) than symbols. I will use capital L and capital S for them.

      Let's say that your number is on A column and starting from A1.

      =IF(A1 L= 250000, 250000, IF(A1 S 1, 0, A1))

      Hopefully this helps.
      Sincerely,
      Antti

  11. This is scenario I am struggling to create a multiple formula:
    1.If AA2 = Y and AB2 = Y results should be "Accrue income"
    2 If AA2 = N and AB2 = N results should be "not Accrue Income" and
    3. If AA2 = Y and AB2 = N results should be " Not Accrue Income and vice versa.
    Thank you for your help

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

      =IF(AND(A2="Y",B2="Y"),"Accrue income",IF(AND(A2="N",B2="not Accrue income"),"N",IF(AND(A2="Y",B2="N"),"Not Accrue income and")))

      I hope my advice will help you solve your task.

      1. Hello.
        I have a spreadsheet that is giving me an issue.
        This what I have and its returning the correct response except for 128:Up with 132: Down
        and it returns "FALSE" if AV5 is blank.
        =IF(AW$5>0,IF(AW132="Down",IF(AW128="Down","Yes",IF(AW128="Up",IF(AW132="Up","Yes","No"))),"Yes"))

        What I want it to do in cell AV134:
        If AV5 is blank then I want AV134 to also be blank.
        IF AV132 AV128 AV134
        Down Down Yes
        Down Up No
        Up Down No
        Up Up Yes
        Thank you for any clarification that you are able to provide.

  12. Hi, Im trying to calculate the following,
    Rep 1 belongs to team 1
    Rep 2 belongs to team 2
    Rep 1 has an individual Count
    Team 1 has an average rate
    for Example:
    If rep 1 Count >=2 or the average rate for Team 1 >=3% then rep 1 will get 5 points, if the cell is blank then zero, if none of these is met then -2 points and same for team 2. this is what i have so far but is giving me an error on the formula:
    =IF(AND(C8="Team 1",$C$30>=3%),5,IF(AND(C8="Team 2",$C$29>=3%),5, IF(D8>=2),5, IF(OR(D8="-",d8=””)0, -2))))
    Hope this is clear, thank you in advance

    1. Nevermind, i was able to figure it out, =IF(OR(AND(C9="Team1",$C$30>=3%),(D9>=2)),5,IF(OR(AND(C9="Team2",$C$29>=3%)),5,-2))
      Thanks anyways, you're examples really helped me.

      1. =IF(D8="",0,IF(OR(AND(C8="Villains",$C$30>=3%),(D8>=2)),5,IF(OR(AND(C8="Heroes",$C$29>=3%)),5,-2)))

  13. N4=IF(G4=1,"H4+I4-L4",IF(G4=2,"H4+I4",IF(G4=3,"H4")))

    Incase of G4=1 Output come, H4+I4-L4 instead of numerical value
    Incase of G4=2 Output come, H4+I4 instead of numerical value
    ...........
    Is there any problem with my if formula? why i dont get numerical value on N Column?

  14. Good Morning! im trying to write a formula that looks at a frequency eg. 6 months, 1 year, 2 year, 5 years and if its X add that number to date inserted in a seperate column. so A would be 1 year, B would be "test date" and C "Due Date" currently have the below, but isn't working.
    =IF(OR(E17="1 Year",EDATE(L17,12)),IF(E17="2 Years",EDATE(L17,24)),IF(E17="5 Years",EDATE(L17,60)))
    Thanks in advance.

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

      =IF(E17="1 Year",EDATE(L17,12),IF(E17="2 Years", EDATE(L17,24),IF(E17="5 Years",EDATE(L17,60))))

      I hope this will help

  15. hello, i would need a bit of a help with formula.
    in sheet1 in column A i have names, they are starting from row 3. in row 2 starting from column B i have numbers from 1 to 31.
    numbers represent dates. so i am filling that table with "1" when someone is there and i leave cell empty when that person was not working that day.

    now hard part.
    sheet 2
    there i need some formula here i will be able to pick number/date and that formula to show me who was working on that day. then to print them, and to pick another date/number.

    thanks

    2. for first 2 dates i have done something like this
      =IF(D2=1,IF(Sheet1!D4=1,Sheet1!C4,""),IF(D2=2,IF(Sheet1!E4=1,Sheet1!C4,""),""))
      do i have to do same for all 31 dates or is there something easier?

  16. Hello! I cannot figure out the formula to "FLAG" a row for if each of the following conditions is not met:
    - If e2= "TRUE", then c2="TRUE" and d2="TRUE"
    - If d2= "TRUE", then c2="TRUE"
    - If c2= "FALSE", then d2="FALSE" and "e2="FALSE"
    - If c2= "TRUE"and either f2 OR g2="TRUE", then d2="TRUE"

    Please let me know if you can help! Thank you

    1. Hello!
      Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  17. I am trying to create a formula for my spreadsheet for multiple ITEM in a single cell
    If there is a "29" in a cell, then formula will D1+E1,
    If there is a "31" in a cell, then formula will D1
    If there is a "32" in a cell, then formula will D1+E1-F1
    ...........

    Here 29,31,32 ... etc area type fabricated rebar size in civil construction.
    so i use following formla
    =IF(H1=29,"D1+E1",IF(H1=31,"D1",IF(H1=32,"D1+E1-F1")))
    For 29 answer shows "D1+E1" instead of (D1=5+E1=7)=12

    Please help!

    1. Hi,
      The reason it is showing D1+E1 is because you put it quotation marks.
      just put D1+E1.

    2. Hello!
      You named the condition "If there is a“ 29 ”in a cell, then formula will D1 + E1". This condition is written in your formula.
      Why do you expect "(D1 = 5 + E1 = 7) = 12" to be executed (I don't really understand what this expression means)?

      1. Hello, Can you help me solve this?

        I need a formula in cell C5 that does the following:
        IF A1+B1 4 but 9 but 15, return $75
        Thanks!

  18. Hi! I need to convert the following from Google Sheets to work in Excel
    =ifs(N9=1,I9/10,N9=2,I9/100,N9=3,I9/1000,N9>3,I9/10000)

  19. Hi,
    Hope you are doing well.
    I wanted to know what is the formula used to derive how many sequences a person has carried out each day. Below is my example..Everyday the person will enter his/her start no. and end of the day the closing no. and i needs to check how many sequences each person has done.

    Thread Tester Seq Started Seq Completed Total Seq.Executed
    MOT-FST-83 MEETA 1 5 5
    MOT-ITM-146 AK-2 0 0 0
    MOT-ITM-147 LD-4 5 5 1
    MOT-ITM-148 LD-3 1 1 1
    MOT-MSA-17 LD-1 33 36 4
    MOT-MSA-18 AK-1 35 36 2
    MOT-MSA-20 AS-2 4 6 3
    MOT-MSA-21 MB-3 6 6 1

    1. Hello Linda!
      To calculate the amount for each person, use the SUMIF function. Use the person's name as a criterion for counting. Read the detailed instructions in this article.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  20. Hi,
    My Name is Pradeep ,I need help for example state and brand wise subscriber count data in a sheet .so my requirement is .
    1st Condition ---
    If I select only state one name then subscriber total count show only selected state sum of count .
    2nd Condition ---
    If state and town then subscriber total count show only selected state and town sum of count.
    3rd Condition ---
    If state and town any name both are no select then subscriber total sum of count show.
    Date for your reference ---
    State Brand Total
    Ayodhya IDEA 188
    Ayodhya Voda 4189
    Gorakhpur IDEA 541
    Gorakhpur Voda 3994
    Kanpur IDEA 2072
    Kanpur Voda 8248
    Lucknow IDEA 1739
    Lucknow Voda 21211
    Prayagraj IDEA 723
    Prayagraj Voda 5028
    Varanasi IDEA 898
    Varanasi Voda 5496
    I am waiting your reply ..

    Thanks
    Pradeep

  21. Hello,

    I'm trying to determing which shipping containers were unloaded within the expected allocated time based on the number of pieces in the container. I have the total time it took to unload the containers in question and the amount of pcs on the containers. What formula can I use to determine if the container was unloaded within the threshold set based of the container pieces?

    Hoping someone can help me!

    < or = 399 pieces = 60 - 120 min per container
    400 - 899 pieces = 120 - 180 min per container
    900 - 1100 pieces = 180 - 240 min per container

    1. Hello Alex!
      Please try the following formula:

      =IF(A1<=399,IF(B1*60*24<=120,"Yes","No"), IF(A1<=899,IF(B1*60*24<=180,"Yes","No"), IF(A1<=1100,IF(B1*60*24<=240,"Yes","No"),"")))

      Cell B1 contains the execution time of the operation in the time format "hh:mm:ss"
      Hope this is what you need.

  22. Hello
    I'm looking for a way to create a formula or macro that will check the value or entry in a column then have it enter the results based on the formula/macro in 2 different columns.
    Example
    Column A Column B Column C
    12345 212456 230456
    12366 217456 (null or blank)

    For the above the criteria it would be IF column A = 12345 then column B = 21256 and column C = 230456
    The data in column A is large with over 300 possible numbers.

    1. Hello James!
      The information presented to you is not enough to give you advice.
      You didn't say anything about the verification criteria. But an Excel formula can only enter a value in one cell.

  23. Hello,
    I have a medical insurance cost comparison. Using data from multiple insurance quotations, with a number of factors to consider (Age, Gender, Salary and insurance Category), what formula would I have to use to return the cost in a specific cell (costing data found on a secondary worksheet, within the same workbook)? For example, Employee A is 30 years old, Single Male earning $4,070 per month and is entitled to Category B Insurance the cost from Insurer 1 = $6,696 per annum

  24. can anyone please help me with this.
    assume i have different data in A1 like 12ABCD999 OR 45JKLM888 OR 78MNOP111 and many more. now i want if value of first 2 number of A1 starts with 12 then my output should be "OK" and if value of first 2 number of A1 starts with 45 then my output should be "GREAT" and if value of first 2 number of A1 starts with 78 then output should be "FANTASTIC".
    Thank you.

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

      =IF(--LEFT(A1,2)=12,"OK",IF(--LEFT(A1,2)=45,"Great",IF(--LEFT(A1,2)=78,"FANTASTIC","")))

      I hope this will help

  25. Hi,
    Can anyone help me with this formula? I've been trying to create a Nested IF OR but just can't get it to work. I have a table with "Team" as a column heading in column A. I've added column D to the table for the office group. If column A is ftl, gai, or orl then column D should be fl. If column A is glf or nol then column D should be gcn. If column A is atl then column D should be geo. This is my formula but the result is always #VALUE!. Please help!
    =IF(OR([@Team]="ftl",[@Team]="gai",[@Team]="orl"),"fl"),IF(OR([@Team]="glf",[@Team]="nol","gcn"),IF(OR([@Team]="atl","geo"))

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

      =IF(OR(A1="ftl",A1="gai",A1="orl"),"fl",IF(OR(A1="glf",A1="nol"),"gcn",IF(A1="atl","geo","")))

      or IF function can be replaced with INDEX + MATCH functions

      =IFERROR(INDEX({"fl","fl","fl","gcn","gcn","geo"},MATCH(A1,{"ftl","gai","orl","glf","nol","atl"},0)),"")

      I hope it’ll be helpful.

      1. You're a Genius! I used the Index Match and it works perfectly! I never would have figured it out on my own. Thank you so much!!

  26. 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,

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

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

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

  30. 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. =today() and =today()+30 for expiry in 30 days (between)
      =today()+30 (greater than)
      Would this work?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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