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

  1. Please i need a formula for the following:
    If "Abbasia" then add 30 minutes to the time (The time is in another cell) and if Maadi Please write the time as it is (The time is in another cell)

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question about IF function.
      To add 30 minutes to the time, use the formula

      A1+TIME(0,30,0)

      I hope I answered your question. If something is still unclear, please feel free to ask.

  2. I want to apply a formula to print purchase order status is active or if print purchase order status is cancel then print cancel. please guide me what is the formula i need to apply to get result

    1. Hi,
      An Excel formula can change the value of a cell. But she cannot start printing the document or cancel printing. To do this, you need to use a VBA macro.

  3. hi all

    i have some data like column a column b column c column d column e
    a1 b1 c1 d1 e1 f1 g1 h1 i1 j1
    1 2 3 4 5 6 7 8 9 10

    if b1="a", then c1 should 2 d1 3 e1 3 like series should continue
    if a1="a", b1="a", then c1 shoud start with 1, d1 2, e1 3 4 5 6 like

    if f1 "a", g1 should start with 6,7,8,9,10............

    A letter it may repeat and continues , but series should be continue.

    ple provide the formula for above logic.

  4. I'm getting a FALSE status on the one logic.

    =IF(AND(V8=1,Q8>7/31/2020,Q8<9/1/2020),R8,"")

    Q8 is the date 9/17/2020
    R8 is a dollar amount

    Logic 3 keeps returning FALSE, but I can't figure out why.

    I'll be copying this down a column where some of the dates fit the parameters.

    1. Hello!
      Here is the article that may be helpful to you: Using IF function with dates
      Please try the following formula:

      =IF(AND(V8=1,Q8 > DATE(2020,7,31), Q8 < DATE(2020,9,1)),R8,"")

      or

      =IF(AND(V8=1,Q8 > DATEVALUE("7/31/2020"), Q8 < DATEVALUE("9/1/2020")),R8,"")

      I hope I answered your question.

      1. They both worked! Thank you so much!!!

        From looking through your site, I had tried the DATEVALUE, but missed using the " marks.

        Thank you again!

  5. I have issues producing a function to fit in my amount cell.
    Ok.
    I have income from 3 sources Beans, Rice and Garri and I can only spend within the income received from each of these items. So I have an income section where I record income received from these items and their totals are in cell (A5, C5, E5 respectively). So For example, if I received 10,000frs as income from Beans, I cannot spend above the 10,000frs.
    I now have an Expenditure section where I have a date column, a searchable list column where I can select the source of income (Beans, Rice, or Garri) i want to spend from at the time being and then a column for amount.
    I want an IF function that when the amount of Expenditure of a particular item is more than the income received for that item, it should give an error message.
    For example. If I received 10,000frs for Beans, and I put the first Expense in the amount of Expenditure section, 5,000frs, choosing Beans as source of income and now if I want to put the second Expenditure on a different date say 6,000frs still choosing Beans as source of income. It should return an error message because is more than 10,000frs income received from that item (Beans)

    Please can someone help me out

    1. Hi,
      Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

      1. Ok. Thanks for the reply Sir. Please How can I get to you may be privately for help in this.

  6. Hello Ma'am

    how can i summary all the fail and pass in the same sheet? I want to separate all the fail and pass after using if function please help. I don't want to manual copy and paste the name the selected fail or pass thank you and GOD bless.

  7. Okay, I have a question. I am trying to pull data from one cell that has a lot of data. IF the data were consistent, I believe it would be easy. But it is not.

    So, I have cell A2 for example the data in the cell is something similar to this:
    Persons Cell Phone Number: 123-456-7890 (not everyone enters it that format and I can't 'force' it to be that way)

    Persons Personal E-Mail Address: (again, not everyone types it out the same)
    -Variations could be Persons Personal E-Mail Address: with or without a space before the start of the email
    Persons E-Mail Address: with or without a space before the start of the email

    All I want to do is easily pull out the persons email address and cell phone number. Here is an actual example of some of the data that could be in ONE cell (the data is not always in this order):

    PERSONS E-MAIL ADDRESS: PERSONS.EMAIL@OUTLOOK.COM
    ________ (SM INITIALS)
    PERSONS CELL PHONE #: (XXX) XXX-XXX
    ________ (SM INITIALS)
    WHERE ASSIGNED TO (SSSSSS) : MR JIMMY SMITS
    OFFICE/CELL PHONE NUMBER: (XXX) XXX-XXXX / (XXX) XXX-XXXX
    E-MAIL ADDRESS: ANOTHER PERSONS EMAIL@OUTLOOK.COM

    IF I AM UNABLE TO CONTACT MY OFFICE, I WILL CONTACT THE HELP DESK AT 1-800-XXX-XXXX.

    1. Hello!
      To extract e-mail from the text, use the formula:

      =MID(A2,SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23, (SEARCH("_",A2,SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23+1)) - (SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23))

      To extract a phone number from text, use the formula:

      =MID(A2,SEARCH("PERSONS CELL PHONE #:",A2,1)+21, (SEARCH("_",A2,SEARCH("PERSONS CELL PHONE #:",A2,1)+21+1)) - (SEARCH("PERSONS CELL PHONE #:",A2,1)+21))

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

      1. Thanks, the problem is that only works if the data is all exactly the same. If the way they enter the title of the persons email address is PERSONS E-MAIL ADDRESS then it might work if all of the other data was in the same location, character wise, in each cell. Sometimes, however, they enter the title of the persons email address as PERSONS PERSONAL E-MAIL ADDRESS: sometimes they enter it as PERSONS E-MAIL ADDRESS: and then to top it off, if they put spaces after the : it throws it off even more.

        1. Hello!
          You can remove extra spaces using any of the methods indicated in this article. But if they enter data in the wrong place, the program cannot fix it.
          However, by changing these formulas, you can extract any part of your text.
          If there is anything else I can help you with, please let me know.

  8. Hi,
    I need to help do this correction and needed to add more like this???

    =IF(C4,(G4*H4*2+G4*J4*2)*L4),IF(C5,((((I5+H5)/2)*G5)*2+(G5*J5+0.141*K5))*L5)

  9. Hi, need help to create a formula that can add 7 numbers (my own given numbers) to generate 5 number combinations between 1 and 36.

  10. hello Team, I'm a bit stuck I need your help. So what I'm trying to do is to put 'if' formula in this way:
    I have 10 persons and 8 cars, when I put a person to work i want to be assign to a car, but when that person is off I want another person to be on that car. The problem that I have is that if I put the first person off the second person will take the car but the will double up.
    My formula is in this way: =if(B=1, A, B=0, C). I also tried =if(B=1, A, B=0, C, C=1, D)
    I think I would need a 'but' somehow but I don't get the hang of it.
    Any ideas would be very helpfull

    thank you

  11. Hello Team, I want to know the formula if 35Km, 45Km, 65KM is their and I want to fixed $7 for 5Km per ride then what was the formula

  12. I have to add 'if A2>=200,B2=200,B2<=0)),"999",A2/B2)

    But, Can I make this formula in IFERROR form ? because in 'value in false' shows #DIV/0!

    Thank you very much in Advance.
    Sean

    1. Hello!
      The purpose of your formula is not entirely clear to me. However, your conditions B2=200 and B2<=0 cannot be met at the same time. If B2=0 you will get # DIV/0 error!

  13. I have multiple sheets with similar information; students fill in one of the sheets but I want the last sheet to look at all the other sheets (at a specific cell) to see which one(s) are filled in and enter that number in the formula cell. The number will be the same regardless of the sheet information entered in. So regardless of which sheet the information was entered on, it should return a specific number.

    1. This is one of the formulas I tried; but I received an error (#value!) - =IF((OR(AP!E5:S5=1, OA!G5:S5=1)), "1", "0"). I am looking at a range of cells in each sheet for a response.

  14. Trying to write a formula for calculation of: 25% of the quantity or (quantity x rate = ) Rs.5000 whichever is more

  15. Hey Ablebits.
    Thx for this amazing page. i've been using you so much. I'm unsure on how I post a question in a new topic. so feel free to move me in the right Direction.

    I have this Formula
    =if(and(D16=C4,D16=C5,D16=C6,D16=C7,D16=C8,D16=C9,D16=C10),B10,"Forkert beløb i D16"))))))))

    How do I fast make changes to this, so that all the times it says "D16" I will change it to "K30"
    Cheers

    1. Hello!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =IF(AND(K30=C4,K30=C5,K30=C6,K30=C7,K30=C8,K30=C9,K30=C10),B10,"Forkert beløb i K30")

  16. Hi
    is it possible to use VLOOUP function in multiple IF statements as below? already try cannot work.

    need your advice if have another way to solve it.

    thanks in advance.

    =IF(EXACT(G5,"AGENT"),VLOOKUP(E5,MASTERLIST!C:G,2,FALSE), IF(EXACT(G5,"MINI AGENT"), VLOOKUP(E5,MASTERLIST!C:G,3,FALSE)), IF(EXACT(G5,"DROPSHIP"), VLOOKUP(E5,MASTERLIST!C:G,4,FALSE)))

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.
      However, the formula contains an error. Try to change it.

      =IF(EXACT(G5,"AGENT"),VLOOKUP(E5,MASTERLIST!C:G,2,FALSE),
      IF(EXACT(G5,"MINI AGENT"),VLOOKUP(E5,MASTERLIST!C:G,3,FALSE),IF(EXACT(G5,"DROPSHIP"),VLOOKUP(E5,MASTERLIST!C:G,4,FALSE))))

      I hope it’ll be helpful.

      1. Thank sir, formula work perfect!!

  17. I have a condition kindly provide me the formula

    upto 10 KM a company charges $20
    above it per km a have additional charges is $2 per KM

    if a person travel following km what was the formula

    A B C D E F
    1 18
    2 10
    3 24

    Then what is the formula I put in B1

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

      =MAX(A1-10,0)*2+20

      I hope it’ll be helpful.

  18. Sorry meant that I wish the span of normal to be between 53.

    My initial thought was fx= IF(A1>=71, "hypertension, IF(A1<=53, "hypotension, IF(A1=53, "normal"))))

    1. Hello!
      Your terms change with every comment. Your conditions A1 <= 53 and A1 = 53 cannot be met at the same time. Perhaps this formula will suit you

      =IF(A1>=71,"hypertension",IF(A1<=53,"hypotension","nornal"))

      Pay attention to the following paragraph of the article above — Multiple IF statements in Excel

  19. Hello!
    Perhaps you can help me. I am hoping to write a IF + AND statement with multiple results.
    A171 is "2" and A1 >53 AND <71 is "0". Thanks!

    1. Hello!
      Please reread the article above, it covers your case completely.
      What does the condition mean - A171 is “2”?

      1. I wish to write fx where A1>71= hypytension, A153=71, "hypertension, IF(A1<=53, "hypotension, IF(A1=53, "normal"))))

        However this does not work for me. Thanks

        1. I wish to write fx where A1>71= hypytension, A153=71, "hypertension, IF(A1<=53, "hypotension, IF(A1=53, "normal"))))

          However this does not work for me. Thanks

  20. Condition:1
    Marks >=20 & Marks =20 & Marks =41 & Marks =41 & Marks <=60
    % Marks 110 & Above
    Eligible for Rs.3000/- reward

  21. Hi

    Can any one apply the formula for given 2 conditions please.

    Condition-1
    Marks 20 to 40
    Marks 41 to 60
    Marks 61 to 80
    Marks 81 to 100

    Eligible Marks 30
    Score (Marks) 30
    % of Marks 100
    Award for 1500

    Eligible Marks 30
    Score (Marks) 35
    % of Marks 117
    Award for 2500

    Condition-2
    Marks 20 to 40
    Marks 41 to 60
    Marks 61 to 80
    Marks 81 to 100

    Eligible Marks 45
    Score (Marks) 45
    % of Marks 100
    Award for 2000

    Eligible Marks 45
    Score (Marks) 55
    % of Marks 122
    Award for 3000

    1. Hello!
      Sorry, I do not fully understand the task. ould you please describe it in more detail? What result do you want to get? Thank you!

  22. Hi can anyone spot the error in the following formula? Thanks.

    =IF(C$7<=55,"37%", IF(55<C$7<=60,"26%", IF(60<C$765,"12.5%"))))

    It gives either 37% (for 55) as outcome.

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

      =IF(C$7<=55,"37%",IF(AND(55

  23. Hello, I am attempting to forecast employee availability based upon a singular calendar date.

    Through conditional formatting I’ve created cells to be filled if they align with the start and end dates based upon a singular employee being able to forecast 10 different start and end dates columns.

    I was able to get all 10 to feed into a singular cell based on a IF(OR(AND statement, however I will only get a 1 if true and 0 if false. For the entirety of the formula...

    My end-state is that I would like to create a data validation that doesn’t allow any date to overlap any of the other dates.

    My thought was if a cell yielded 1, great! If the cell went greater than 1, then the user would get an error message stating the date overlaps with another.

    Unless you know a better solution? I am all ears, Grand Master Yoda :)

    Please Help
    Warm Regards, Michael

    1. Hello!
      Unfortunately, without seeing your data it is impossible to give you advice.

      I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

      We'll look into your task and try to help.

  24. Hi Friends

    Could you please help me to get the excel formula?

    c11 = 50% of b11 or 100000, which one is lower

    Thanks
    Manik

  25. Good morning Svetlana,
    I really need some help on this,
    How do i write a formula to convert every 0.30 to 1.
    Assuming cell a1 contains 100, how do i count very 30 as 1.

    I hope this makes sense and I really appreciate the assistance!!
    Thank you in advance for the help!!
    Kind regards,

    Samuel

  26. Hello
    i have a list of names, scores, sex and statuses and here's my assignment:

    if the "status" of the customer is "Faculty" then give it a value with "Dr. " plus a space plus First Name plus a space plus Last Name.
    if the "status" of the customer is "student" and the "sex" is "M" then give it a value with "Mr. " plus space plus first name space plus last name
    if the "status" of the customer is "student" and the "sex" is "F" then give it a value with "Ms. " plus a space plus first name plus space plus last name

    please help me out with that one i am completely clueless on how to do it..

  27. Hi would you be able to help me with the below. I have 2 formulas that work separately however want to merge them into one formula.

    =IF(OR(AND(SUM(W3:X3)>=100,Y3>=0), AND(SUM(W3:X3)<=100, Y3<=0)), "Yes", "No")

    =IF(W3="","N/A", IF( X3= "", "N/A", IF(Y3="", "N/A")))

    I would like to add into the first formula that if W3 or X3 or Y3 are blank, then it should return "N/A"

  28. If you write 5 column like this

    A.Name
    B. Source (broker,ref and direct)
    C .product value
    D. commission %
    E. Commission amount( need formuls this column)

    1 ) if any broker(column B) sale 1 product
    Get commission (product value* 2%)=

    2 ) any existing buyer through his refrence sale 1 product to any customer
    Get commission 3 types
    * 1 st deal = 10k
    * 2 nd deal = 20k
    * 3 rd deal = 30k

    And

    3) if direct sale any product
    Get 0 commision

    How to codition formula in one cell

    1. Hello!
      Your task is not completely clear to me. Explain condition 2 using your table data as an example. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  29. Hi, i want determine the latest date for this:
    If: a1=complete, c1=complete, e1=complete,g1=complete, i1=complete, if all of these are in "complete status" i want to get the latest date: b1=2/1/2020,d1=3/2/2020,f1=4/2/2020,h1=5/1/2020,j1=6/1/2020,j1=7/1/2020

  30. Please can someone help me with an excel formula? I have a cell E6 which has a drop down (Not Applicable, Fully Implemented, Mostly Implemented, Partly Implemented and Not Implemented). I want cell E7 to output 100%, 100%, 75%, 50% and 0% when E6 is Not Applicable, Fully Implemented, Mostly Implemented, Partly Implemented and Not Implemented respectively.

  31. I want to have solution of the following, if some one can help me:

    Cell A1 = Professional
    Cell A26 = sum of figures from cell A2 to A25
    I want to have results in A30 that if cell A1 is equal to "Professional" and results of A26 is less than or equal to 400 than it should display the results of A26 otherwise 400 should be there,

    Hope anyone of you can understand my query

    Naeem Sabir

  32. Is there a way to make this Countif statement work?

    =COUNTIF(C11:C112,"Low", AND D11:D112,"Work in Progress")

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. I am assuming you have not one but several conditions to count. Therefore, I recommend using the COUNTIFS function. Read this detailed instruction.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  33. guys i've spend 5 hours. I just can't figure this out, so i seek help from you and god himself.
    I want to make an IF statement between theese numbers.
    7% $1,000.00
    8% $10,000.00
    9% $50,000.00
    9.50% $100,000.00

    how do I make if between 1000 and 9999 then show 7% or between 10000 and 49999 then show 8% etc.. etc...

    all my love

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

      =IF(A1>=100000,9.5%,IF(A1>=50000,9%,IF(A1>=10000,8%,7% ) ) )

      Hope this is what you need.

      1. I have another great question which is even harder to figure out.
        I got this cummulative calculations which starts with
        5% of all up to 10,000.00 $
        10% of all between 100001 and up to 20,000.00 $
        12% of all between 20001 up to 50,000.00 $
        etc. etc..

        how do I make that calcualtion so it won't keeping make 5% of all after 10k but still SUM the all the differnet percentages?

        thx for your time and help. how can I share your knowledge with the community?

        1. Hello!
          You have not described all the conditions and the expected result, but I think the following formula will suit you:

          =MIN(A1,10000)*5% + MAX(0,A1-MIN(MAX(0,A1-20000),30000) - MAX(0,A1-50000)-10000)*10% + MIN(MAX(0,A1-20000),30000)*12% + MAX(0,A1-50000)*12%

          I hope it’ll be helpful.

      2. Thx for your fast reply. I just figured it out my self.
        the "=IF" functions does not work. I ahve to use an "=IF(And(" function.
        so the code gonna look like this
        =if(and(K14=F12,K14=F13,K14=F14,K14=F15,K14=F16,K14=F17,K14=F18),E18,"Forkert beløb i j14"))))))))

        Thx for helping it's much appreciated

  34. I was looking all over on how to code the IF AND OR formula for different shifts base on a time and your example on the grades showed me the way.
    =IF(ISBLANK(D1),"",IF(AND(D1>=TIME(6,0,0),D1=TIME(14,0,0),D1<TIME(22,0,0)),"2nd Shift","Night Shift")))
    There may be a better way, but this is working
    Thanks

  35. I was wondering if you could help me? I am trying to figure out what is wrong with this formula. I keeping getting the #VALUE! error.

    =IF((AND(G2=OR("AF","SA"),(F2="R"))),"5.0%",IF(AND(G2=OR("AF","SA"),(F2=OR("S","F"))),"6.0%",IF((AND(G2=OR("NA","EU","AS","OC"),(F2=OR("R","S","F")))),"6.0%")))

    Thanks in advance!

    1. Hello!
      Your formula is incorrect. Pay attention to how to write the AND and OR conditions correctly. See above Example 3 - Using IF with AND & OR functions.

      G2=OR(“AF”,”SA”) - incorrect

      OR(G2=“AF”,G2=”SA”) - correct

  36. Hello,

    I have a table with multiple columns - Gift, Type, Date, Year Since Gift & Exempted.

    Under Type there is a 2 option dropdown menu. - Charity and BPR.

    I have got the Year since gift to automatically calculate the years.

    What I would like to be able to do is have the Exempt column show "yes" or "no" if the gift is exempt.

    If the Type column shows "Charity" it is exempt, so the exempt column would need to show Yes.
    If the Type column shows "BPR" and it has been more than 2 years in the years since gift column it would be exempt, so would need to show "Yes". If its not exempt, I would like the column to show "No".

    This is the formula that I have used, but it does not work correctly.

    =IF(OR(AND(F14="BPR",H14>2),OR(F14="Charity",H14>=0)),"Yes","No")

    Please can you advise.

    Thank you!

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

      =IF(OR(AND(F14="BPR",H14>2),AND(F14="Charity",H14>=0)),"Yes","No")

      I hope this will help.

      1. Hi,

        Thank you for this!

        I am however having the same problem. It works correctly for the BPR output but if I change the dropdown to Charity it does not change.

  37. I'm trying to work our a formula to add times to different delivery methods. for example . if seafreight add 42 days to dispatched date. if Airfreight add 14 days to dispatched date.

    any ideas

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

      =IF(B1="seafreight",A1+42,IF(B1="Airfreight",A1+14,""))

      1. Thanks your so much

  38. Hi

    I would want a formula to sum the multiple cells only in case whereever it is mentioned as roll up.

    MatchID Status Amount
    12345. Roll up HERE I need to have the sum the amount of below two rows
    12345. 100
    12345. 100

  39. Hello,

    I wonder if you can help? I am trying to get a score in excel. We have to score clients on the amount of outgoing they spend. For example if someone has an outgoing <2000 they get a score of 0, if they have -1000 to -5000 they get a score of 1, if they have -5001 to -10000 they get a score of 2.

    Does that make sense? Basically scoring someone on their outgoings but I don't know how to do this with minus figures and when someone has a from to figure.

    Any help would be great.

    1. Hello!
      Explain your terms. The amount 1500 refers to both a score of 0 and 1.
      Please describe your problem in more detail. It’ll help me understand it better and find a solution for you.

  40. I was able to do a work around of some sort that I'm okay with using for now. Id still like to know why its failing but I'm okay for now. This is my work around formula below:

    =IF(A9="REC 365",IF(C9="5 Year 4.99%",IF(E9="A 3.30",'REC Data Set'!A3:G25,"")))

    When the "A" is introduced, it works fine. Very strange.

    Thanks for listening to me complain LOL

  41. I am trying to put a formula together that is checking to see if 3 separate criteria's are met, and if they are met, it will populate data from another sheet in the same document. I've tried multiple formulas but they aren't exactly working. Please see below:

    =IF(A9="REC 365", 1 * IF(C9="5 Year 4.99%",'REC Data Set'!A3:G25,"")) ---This one works but when i introduce the third criteria, it fails:

    =IF(A9="REC 365", 1 * IF(C9="5 Year 4.99%", 1 * IF(E9="3.30",'REC Data Set'!A3:G25,"")))

    I got closer with the AND argument but it doesn't work exactly, just gives me a value of false
    =AND(A9="REC 365", AND(C9="5 Year 4.99%", AND(E9="3.30", 'REC Data Set'!A3:G25,"")))

    Any help would be greatly appreciated. Thank you!

    1. how can u get result? you asking for result with many cells in one cell.. check again

      1. Hi,
        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.

    2. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets.
      I assume that you have a number in cell E9. You are comparing it to the text "3.3". Write down the condition E9 = 3.3
      I hope my advice will help you solve your task.

      1. Good Morning! Thank you for your reply!

        I understand its difficult to decipher what I have going on here, and I appreciate your assistance.

        I do have 3.30 in cell E9. I have also tried 3.3. Is there anyway you could (or be willing to) take a look at my document so you can see what I'm doing wrong? I have a formula that works for everything until it evaluates the last part for cell E9. Please see below:

        =IF(A9="REC 365",IF(C9="5 Year 4.99%",IF(E9="3.3",'REC Data Set'!A3:G25,"")))

        If I remove the E9 portion the formula works fine. I am out of ideas at this point and I've even gone in and tried referencing different cells and removing formatting from E9 and trying to use different cells instead of E9 to try everything I can think of. Please let me know if you are willing to take a look.

        Thanks again either way!

        1. Hello!
          I already wrote to you that you are comparing text with a number. Change the formula

          = IF(A9 = "REC 365", IF(C9 = "5 Year 4.99%", IF(E9 = 3.3, 'REC Data Set'! A3: G25, "")))

          1. Thank you for your help!

            1. =IF(A9="REC 365",1*IF(C9="5 Year 4.99%",RECDataSet!A1,""),"time to go")

              this can work

        2. I want to add that it has something to do with E9 being a number. When I change the formula to this:

          =IF(A9="REC 365",IF(C9="5 Year 4.99%",IF(L10="G",'REC Data Set'!A3:G25,"")))

          It works perfectly. For some reason the formula fails when it involves numbers of any sort.
          L10 is just a random cell i used for testing and populated it with the value G. Any ideas on why it would fail with it being a number?

          Thanks again!

          1. how can u get result of many cells in one cell?

    3. I have also tried this method:

      =IF(AND(A9="REC 365", C9="5 Year 4.99%", E9="3.30"), 'REC Data Set'!A3:G25)

      I'm getting a result of FALSE because something about this formula isn't true? I don't understand. All criteria is being met, this formula should at the very least evaluate as true.

  42. HI I M REQUIRED FOLLOWING FORMAT COMPLETE: -

    I HAVE THREE COLMS

    B=CW C=PW D=DIFFERENCE (CW-PW)

    I HAVE REQUIRED A FORMULA:
    IF D IS MORE THAN B SHOWS CALL, D IS MORE THAN C SHOWS PUT, IF B AND C FIGURES DIFFERENCE NOT DOUBLE THEN SHOWS SIDEWAYS.

    1. Dear Sir/ Madam

      I m using under mention formula

      =IF(OR(B2>C2,C2C2,C2<B2, C2=B2), "put", "call","Sideways")) not works,

      I want if C2=B2 difference is not double shows sideways also shows sideways

      1. Hello!
        Your formula is wrong. The information you provided is not enough to understand your case and give you any advice. Please provide me with an example of the source data and the expected result.

    2. Hello!
      Your task is not completely clear to me. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you

  43. Hi,

    Amazing work Ablebits.com..

    I try the formula here:
    =IF(S4,"SUBMITTED","NOT SUBMITTED")

    S4 represent the date of actual submission. I need to add another criteria "Ready by QC" in the formula.
    If not mistaken, I need to have another date for "Ready by QC", correct?

    Example:
    Column A: Date Actual submission is: 20-09-2020
    If the actual date key in, column C will turn to "Submitted", If empty, it will appear "Not Submitted"

    Column B: Date sent for QC: 18-09-2020
    Column C will turn to 'Ready by QC". If not key in the date column B, it will turn to submitted.

    i am not sure if this the correct explaination.

    1. Hello!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =IF(B1<>"","Ready by QC",IF(A1<>"","Submitted",""))

      Hope this is what you need.

  44. Question No. 2

    STELCO

    Emp ID Emp Name salary Emp Type HRA TA PF Bonus Total
    ST001 Amir Khan 8563 1
    ST002 John Abraham 5320 2
    ST003 Akshya 6586 3
    ST004 Dileep 14521 4
    ST005 Santhosh 4500 5

    HRA Salary>=6000 20 %of sal 1000
    TA Salary>=6000 2000 1500
    PF Salary>=8000 14 %of sal 8% of sal

    1. Hello!
      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.

  45. Hi,

    i am working with the Excel 2016 and I need a formula that will check if the the name in the first column is consistent with the names in the column alongside.
    For instance:
    Column B: Fruit, Vegetable; Beef.
    Column C: Strawbarry, Apple; Potatoes, Salad greens Spinach, Turnips, Onions, beef, pork, sausage, veal, chicken

    Fruit: strawbarry, apple;
    Vegetable: Potatoes, Salad greens Spinach, Turnips, Onions,
    Meat: beef, pork, sausage, veal, chicken

    I need to check with a third column (D) if the column B is Fruit and the column C is Strawbarry, the check is correct --> "Ok"; while if the column B is Fruit and the column C is chicken the check will be negative --> "Non ok".

    Please help me with this.
    Thanks

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

      =IF(A1=(IF(SUM(--(B1={"strawbarry","apple"}))=1,"Fruit", IF(SUM(--(B1={"Potatoes","Salad","greens","Spinach","Turnips","Onions"}))=1,"Vegetable", IF(SUM(--(B1={"beef","pork","sausage","veal","chicken"}))=1,"Meat","" ) ))),"OK","NOT")

      The fastest and correct way to determine which species a product belongs to is to use the VLOOKUP function. But your information is not enough to give you advice on its use.

  46. What if i want to seperate date column into 5 combinations such as "0-30 days", "31-60 Days", "61-90 Days", "91-120 Days", "120 Days n Above".

    Thanks

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

      =IF((D1-TODAY())<=30,"0-30 days", IF(D1-TODAY()<=60,"31-60 days", IF(D1-TODAY()<=90,"61-90 days", IF(D1-TODAY()<=120,"91-120 days", "120 Days n Above") ) ))

  47. Hello,

    I'm working on formula..

    There is three input strings and based on that i need to find the answer... for i.e.. Box size - 8ft / Application - Chill / Door opening - Limited - for these inputs answer should come as C150e or any one input wrong answer should come as NA.

    1. Hello!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =IF(A1*B1*C1=8,"C150e","NA")

  48. I am working with Excell 2010 and I have a sheet like bellow

    Cement, Brick, Sand (50 Item) = these all item will show as "Civil"
    Wood, Board, Paint (15 Item) = these all item will show as "Carpentry"
    Glass, Aluminium, Lock (10 item) = these all item will show as "Aluminium"
    Paint, Polish, PaintWages (5 Item) = these all item will show as "Paint"
    and many more.

    I put a formula like it.
    =IF(OR(A1="Aluminium Work (WIP)",A1="Aluminum & Glass work (HTL - Advance)",A1="Glass Materials ( WIP )"),"Aluminium",IF(OR(A1="Door Frame",A1="False Celling (HTL Febricator Advance)",A1="Flush Door",A1="Miscellaneous (Carpentry)",A1="Plastic Door",A1="Receiption decorational expenses (wip)",A1="Solid Door"),"Carpentry","")))

    But After putting more then 25 logic it is not working, please help me.

    1. Hello!
      The IF function has a limit on the number of conditions. I recommend using a 2-column table in which each product has a corresponding name. For example, Cement - Civil, Brick - Civil. With the VLOOKUP function, you will insert the name from this table into your main table.
      Read how to do a Vlookup in Excel.
      I hope my advice will help you solve your task.

  49. Hi
    Please help me with following
    If column A and B both contains apple the answer should be apple
    And in the same way if both columns contains banana then also i need to get apple
    If in column A is apple and in column B is banana answer should be banana and vise versa
    Kindly help me using excel formula for this

    1. =IF(OR(AND(A1="APPLE", B1="APPLE"), AND(A1="BANANA", B1="BANANA")), "APPLE", "BANANA")

    2. Solution to Apple and banana conditions

      =IF(OR(AND(A1="APPLE". B1="APPLE"). AND(A1="BANANA". B1="BANANA")). "APPLE". "BANANA")

  50. Can you help me? I need a formula that will do the following and will also need to meet multiple conditions:

    If A1="Contract",B1="Cold" and C1<=1 year from today's date, display 7.5%, but if A1="Contract",B1="Cold" and C1<=2-4 years from today's date, display 3%.

    A1 can be: Contract, Casino or Forms
    B1 can be: cold or warm
    and the percentages for all change based 1-4 years from today's date

    I can't figure out how to incorporate all of the conditions within one formula.

    Thank you so much!

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

      =IF(AND(A1="Contract",B1="Cold",DATEDIF(TODAY(),C1,"y")<=1),7.5%,IF(AND(A1="Contract",B1="Cold",OR(DATEDIF(TODAY(),C1,"y")>=2,DATEDIF(TODAY(),C1,"y")<=4)),3%,))

      To find the difference between dates, use the DATEDIF function.

      I hope my advice will help you solve your task.

      1. Thank you for your help. I put the formula in and changed the cells and I am receiving a #NUM! error.

        1. Hello!
          The formula I sent to you was created based on the description you provided in your first request. You wrote the conditions in the second paragraph.
          In order to prevent it from happening, please provide me with the detailed description of your task. Any examples of the source data and expected result would be of great help. It’ll help me avoid further confusions and find the right solution for you.

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