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

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

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

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

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

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

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

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

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

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

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

  11. Any Body can correct this formula

    =IF(ISNUMBER(SEARCH("011","012",014","017","061","076",077","078","085","089","092","095","099"
    ",C2)),"Cellcard") IF(ISNUMBER(SEARCH("010","015","016","069","070","081","086","087","093","096","098",C2)),"Smart")
    IF(ISNUMBER(SEARCH( "088","097","071","031","060","066","067","068","090",C2)),"Metfone")

    1. Hello!
      Could you please describe your task in more detail? What result do you want to get? Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you

  12. i have a problem hope you can help me.

    I want to construct a if statement such that

    A1=10000
    B1= 12 or 24 or 36
    C1 =??

    i want to automatically calculate if B1=12, C1=A1/2, if B1=24, C1=A1 and B1=36, C1=A1*1.5

    Can someone help me?

    Many thanks

    1. Hello!
      Please try the following formula:

      =IF(B1=12,A1/2,IF(B1=24,A1,IF(B1=36,A1*1.5,"")))

      or

      =IFS(B1=12,A1/2,B1=24,A1,B1=36,A1*1.5)

      I hope this will help

  13. Hi ,
    I wondered if you could help me with the following problem!
    =IF($I$9=$A$3:$A$40,$C$3:$C$40,if($I$9=$B$3,$C$3:$C$40,0)

    Can anyone please assist?

    1. Hello!
      Your formula is missing parentheses.

      =IF($I$9=$A$3:$A$40,$C$3:$C$40,IF($I$9=$B$3,$C$3:$C$40,0))

      Is this the problem?

  14. Solve the the issue in the equation
    =IF(OR(H8>=6,P8>=40%)2000, 2500, IF(OR(H8>=5,P8>=40%), 1500, 2000, IF(OR(H8>=4,P8>=40%), 750, 1500, IF(OR(H8<4,P8<40%),0,0))))

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

  15. Say A1, A2, A3 and A4 cells with 100 characters
    if you make the following formula

    =IFS(1=1,CONCATENATE(A1,A2,A3,A4))

    it throws a #Value error, I believe because A1...A4 exceeds the 255 text length

    If you use
    =IF(1=1,(A1 &A2 &A3&A4))

    then it works as a charm

    Anybody knows why this happens?

    2. I mean by the second formula

      =IF(1=1,CONCATENATE(A1,A2,A3,A4))

      IF function is working, not IFS

  16. I have rows with some data in column A the same and need to combine data in column B whenever the data in column A is the same.
    What formula can I use to summarize this data? A pivot table summarized the data, but it’s still shown in separate cells. I need to have one cell per column A answer.

  17. Thank you that worked perfectly. The calculating cell contains FALSE until all conditional are met. Is there a way FALSE can be blank until all conditions are me?
    Thanks you

    1. Hello!
      If I understand your task correctly, You can use something like this formula —

      =IF(A1>B1,A1,"")

      Use "" instead of FASLE.

  18. =IF(A4="big creek",“Karen”,IF(A4="vista AOR","Jim",IF(A4="alhambra","Martin")))

    I know I can't use * for remaining characters with IF function. Is there a way to use multiple IF statements with partial texts and not case sensitive? The above did not work.

    Really appreciate the help!

    1. =IF(K:K="published","Live-API",IF(K:K="blocked","",IF(K:K="published","Live")))

      Can I use the OR condition here?

      Live-API or Live

      1. Hello!
        There are 2 conditions in your formula

        K:K=”published”,”Live-API”
        and
        K:K=”published”,”Live”

        The information you provided is not enough to understand your case and give you any advice, but this is wrong.

    2. Hello!
      Instead of the "* big *" condition, you can use the formula to find the value in the text

      ISNUMBER(SEARCH("big",A4,1))

      I hope my advice will help you solve your task.

  19. Good afternoon,

    I am having great difficulty trying to fix some else's spreadsheet...this never happens of course. They are gardeners so the fact that they managed to fire up a computer and enter the data is pretty impressive and not in their wheelhouse....nor is Excel in mine.

    The problem. I am needing to do a secondary sort on data. The parameter is "soil conditions" is the secondary column header.
    The range are currently all text values entered individually in several hundred cells:

    dry
    dry - avg
    average
    avg - moist
    moist
    avg - wet
    wet
    submersed

    I need to be able to assign a numeric value based on the range in this order, 1 through 8. The IFS argument does not appear to work, nor does IF in series.

    Any help you can give would be greatly appreciated.

    Cheers,

    Richard

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

      =VLOOKUP(A1,{"dry",1;"dry – avg",2;"average",3;"avg – moist",4;"moist",5;"avg – wet",6;"wet",7;"submersed",8},2,0)

      The VLOOKUP function selects the desired condition from the array.

  20. Column A Column B Return
    aa p P and H
    aa h P and H
    aa p P and H
    aa p P and H
    bb h H
    bb h H
    bb h H

    I have a sheet like this above, Column A has ID#s and column B has different values for tht same ID, in column C i want to combine the values of Column B for each ID in column A and return those values in Column C. PLEASE HELP ME WITH THIS.
    Thank you

    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.
      It is not entirely clear what data is in each column. Explain what is the identifier in your data?
      Please provide me with an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  21. Hello, i have three equations.
    In sales team monthly target assigned, if salesman achieve less than 60% comm. will b zero, if 60-80% 2.5% comm. & more than 80% 2.5+1.5%(team leader1.5%)
    Please help me !!!!!

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail?
      What is "2.5% comm"? What does "2.5 + 1.5% (team leader1.5%)" mean?

      1. Lemme explain you again ... we have a sales team with monthly commission assigned, if salesman achieve less than 60% of his target then commission will b zero, if he achieve 61-80% of his target will 2.5% of commission on same, if he achieve more 81% and above will get 2.5% commission & his team leader will get 1.5% commission ...
        hope its clear, pls help urgently

        1. Hello!
          If cell D1 contains the monthly sales target, and cell G1 contains the total sales of the seller, then the commission formula might be:

          =IF(G1/D1<0.6,0,G1*0.0025)

          For the team leader -

          =IF(G1/D1<0.8,0,G1*0.0015)

  22. Hi, Im trying to create a formula using the IF Function with multiple conditions.
    My scenario is that if
    Q1= Yes and Q2, Q3, Q4, Q5 and 6 = Yes or No the outcome = Significant.
    But if Q1 = No and Q2 or Q3 or Q4 = Yes the outcome = High (it doesnt what Q5 and Q6 equal)
    And if Q1, Q2, Q3 or Q4 = No but Q5 or Q6 = Yes then the outcome = medium
    And if all questions = no then outcome = low
    Thank you

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      Please try the following formula:

      =IF(AND(Q1="Yes",OR(Q2="Yes",Q2="No"),OR(Q3="Yes",Q3="No"), OR(Q4="Yes",Q4="No"),OR(Q5="Yes",Q5="No"),OR(Q6="Yes",Q6="No")),"Significant", IF(AND(Q1="No",OR(Q2="Yes",Q3="Yes",Q4="Yes")), "High",IF(AND(OR(Q5="Yes",Q6="Yes"), OR(Q1="No",Q2="No",Q3="No",Q4="No")),"Medium", IF(AND(Q5="No",Q6="No",Q1="No",Q2="No",Q3="No",Q4="No"),"Low",))))

  23. Hi! I am trying to create a royalty % using the IF formula with multiple conditions.
    My scenario is that I have 5,000 patients in this 5 year plan. I am trying to come up with a 2 formulas broken out in two Tiers. The first tier or formula is 1,000 patients onboarded in Year 1 receive a 20% royalty. The second tier is royalty percentage for cumulative onboarding or remaining 4,000 patients receive a 3% royalty. BUT if we add new patients in year 3, those patients receive the 20%.

  24. I need specific formula for this problem
    I check attendance 3 times: 1st , 2nd, 3rd
    Now I would like my excel to mark LATE if the student:
    Got the 2nd and 3rd attendance but not the 1st
    and
    Got the 1st and 3rd attendance but not the 2nd
    and
    Got the 1st and 2nd attendance but not the 3rd

    1. Hello!
      I assume that if the attendance is negative, then the cell is empty. You can use the formula

      =IF(SUM(--ISBLANK(A1:A3))=1,"Late","")

      I hope it’ll be helpful.

  25. Hi! I am trying to create an IF formula with multiple conditions.
    My scenario is that I have 5,000 patients in this 5 year plan. The first 1,000 patients onboarded in Year 1 receive a 20% royalty. After that, the remaining 1000 patients onboarded receive only a 3% royalty. BUT if we add new patients in year 3, those patients receive the 20%. Let me know if I explained it correctly.

    1. To add: I am looking for a royalty % using the IF formula with multiple conditions.

      I am trying to come up with a 2 formulas broken out in two Tiers.

      The first tier is a royalty percentage the moment patients get enrolled in the model.
      The second tier is royalty percentage for cumulative onboarding of patients.

      1. Hello!
        Unfortunately, without seeing your data it hard to give you advice.
        For the condition in the IF function, I recommend using the COUNTIFS function. With COUNTIFS, you can count the number of patients in a given time. I recommend examples of using COUNTIFS.

        1. Hi! No problem, would the below data be helpful? Here is a financial model with the following scenario

          Contract value: $30,000,000
          Years: 5 year model
          Number of patients: up to 6,000
          Revenue per patient: 5,000
          Royalty to patients: Right now its fixed at 20% but I would like to base it on patients enrolled
          Gross Margin per patient: $2,062
          COGS: $2,938 (includes a 20% fixed royalty)
          Gross Margin: $12,373,920
          Gross Margin %: 41.2%

          I am struggling with coming up with a COUNTIFS formula for "royalty to patients"

  26. Hi there
    I#m trying several nests in my formula, but something is not working... And I'm not sure what can it be
    =IF( (D52>2); (if(2900>I52>5000); (if(J52="FC");"yes";"no");"no"))

    1. Sorry, this is the one I think should be ok
      =IF( ((D52I52>5000); (if(J52="FC");"yes";"no");"no");”no”)

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

  27. Hi,
    please help me out

    i have so many column from A to N
    i want to add value from A C D F H J L N
    COLUMN B E G I K M CONTAIN DIGIT 1 TO 4
    IF VALVE 1 OR 4 COME THEN IT ADD A AND C OR STOP TO ANOTHER COLUMN
    IF VALUE 3 OR 2 COME THEN ADD A C D AND AGAIN SEARCH VALUE.

    ONLY 1 OR 4 COME MEAN IT WILL STOP ADDING NEXT COLUMN OTHERWISE 2 OR 3 COME MEAN IT ADD ANOTHER COLUMN AND SEARCH FOR NEXT COLUMN.

    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.

      1. a b c d e f g h i j k
        19 3 15 2 11 2 16 1 16 3 15

        i want to add 19+15+11+16 when 2 or 3 come after any number.
        if 1 or 4 come then stop adding another column

        1. Hello!
          Explain the phrase "i want to add 19 + 15 + 11 + 16 when 2 or 3 come after any number." Why are these cells selected? Where do you enter 2 and 3?

  28. Hello, i have three set of data inputted manually. I have another column for the set of data to be copied if i input a character corresponding to that set of data.
    Example:
    A , B , C , D
    1 Q , R , S ,
    2 10, 19 , 7
    3 5 , 11 , 66 ,
    4 1 , 9 , 3 ,
    5 23 , 3 , 8 ,
    i want to type into D1 "1" for all the data under Q (i.e A2:A5) and it will copy that data into column D. If i type in "2" into D1, i want all the data under R to be copied in the column D. And if i type in "3" into D1, it will copy all the data under S into column D.
    Is this possible?
    Thank you in advance

    1. Hello!
      Column D in your case already contains data. Therefore, you can only replace this data with values from other cells using a VBA macro.

      Your phrase "And if i type in“ 3 ”into D1, it will copy all the data under S into column D." looks strange since this data is already written to column D

      1. Thanks for your response.
        D is meant to be a blank column. I think the spacing is making it look like it has data. D1:D5 is all empty. I want to put my formula in E1 so that the data will be copied onto column D.
        A , B , C , D , E
        1 Q , R , S ,
        2 10, 19 , 7
        3 5 , 11 , 66 ,
        4 1 , 9 , 3 ,
        5 23 , 3 , 8 ,
        Hope this is clearer.
        Thank you in advance for your time.

  29. HI,
    Please help me with formula using ifs
    Total marks is = 30
    Time limit to complete the exam is 10 mins , prior to which following deductions will be made :
    If the person completes the exam in 11 mins, 15% marks will deduct
    from the total marks.
    If the person completes the exam in 13 mins, 30% marks will deduct
    from the total marks.
    If the person completes the exam in 15 mins, 50% marks will deduct
    from the total marks.
    If the person exceeds 15mins, the marks will be nil.

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

      =IFS(B1>15,0,B1>13,C1*0.5,B1>11,C1*0.7,B1>10,C1*0.85,B1<=10,C1)

  30. A+A+A=3.How is possible in MS Excel work sheet

  31. How to distribute numbars according to slabs, and short upto 100 with different rates as well

    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.

  32. Hello,

    Below is column A, B & C and in column D I need is.. if A1 is equal to "UKB" and then if B1 is equal to C1 the field should be blank or it should say "PLS CHECK" and if A is not equal to UKB then Column D field should be blank.

    UKB M07595448 M07595448
    UKB M07595448 M07595448
    UKB M07748431 M07748431
    UKB M07744464 M07744464
    UKB M07744464 M07744464
    UKB M07685293 M07685293
    UKB M07685293 M07685293
    UKB M07685293 M07685293
    UKB M07794223 M07794223
    UKB #N/A M04787723
    UKB #N/A M04395096
    UKB #N/A M04395096
    UKB #N/A M04395096
    UKB #N/A M04395096
    UKB #N/A M02083964
    UKB #N/A M02083964
    UKB #N/A M02083964
    UKB #N/A M01861464
    UKB #N/A M01861464
    UKB #N/A M01861464
    UKB #N/A M01861464
    UKB #N/A M01861464
    UKB #N/A M01861464
    UKB #N/A M04802877
    301219 #N/A M02731841
    301219 #N/A M02731841
    301219 #N/A M02731841
    301219 #N/A M02731841
    290120 #N/A M08189356

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

      =IF(A1="UKB",IF(B1=C1,"PLS CHECK",""),"")

      I hope this will help

      1. Actually, I have multiple conditions. Firstly, if A1 is equal to "UKB" then it should check whether B1 is also equal to C1 and B1 & C1 matches then the D1 field should be blank and if B1 & C1 does not match then it shoudl display as "PLS CHECK" and suppose if A1 is not equal to 'UKB" then the field should be blank.

        1. Hello!
          The formula I sent to you was created based on the description you provided in your first request. Why couldn't all the conditions be described at once? I think that you yourself can add all the conditions that you have to my formula.

          1. I tried but not able to get it. Pls help me.

  33. If a1 contain 60 and I input any value in a2 which cannot be divided by A1 as a whole number (eg. 120/60=3) as correct and (140/60=2.33 or 30/60=0.5) as wrong. How can I check for these and then advise person entering the a2 data that his value does not fall into the allowable multiples of 60 lots

    1. Hello!
      You can use this formula for Data Validation or to check a condition

      =IF(MOD(A1,60)=0,TRUE,FALSE)

      I hope it’ll be helpful.

  34. I have a spreadsheet with 10 years and am trying to blend in raises over that time to figure an amount we can afford each year. Current Wage versus what they should be at (new wage). So, if current wage is 10 and new wage is 15, he is 50% behind. I have 100 employees and have 4 tiers to try to catch them up depending on how far behind whereby those that are further behind will get a bigger pay increase. My problem is they need to be more than tier 1 behind but less than tier 2 and, if the percentage increase brings them over the new wage, they should only get up to that new wage. G3 is the current rate and N3 is the new rate. O3 is the percentage behind they are. Q1,R1,S1,T1 are where I'm figuring the percentage behind is what will give them the % raise (which is Q2, R2, S2, T2). Help please?

    G=IF(OR(G3>N3,G3),IF(OR(O3=$Q$1,O3=$R$1,O3=$S$1),(G3*$S$2)+G3,IF(OR(O3>=$T$1),(G3*$T$2)+G3,G3))))))

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What do you want to calculate with this formula? What result do you want to get? Give an example of the source data and the expected result.
      Thank you!

  35. hi
    I want to set a criteria when met by three scenarios e.g. if extraction % is greater than 80%,waste % is greater than 60% and status is ready, Give me Sample the Area.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Thank you!

  36. Never mind this one

  37. Hello! I have another problem with my formula again, because my boss wants to add some details:

    Column A have either "Oranges" or "Appleas", Column B have either "Fresh", "Moderate", or "Mature", I'll input a number on C, and result on D

    If A1 is "Oranges"
    (a) If B1 is "Fresh",
    (1) If C1 is greater than or equal to 50, C1 should multiply to 3
    (2) If C1 is less than 50, C1 should be subtracted by 10
    (b) If B1 is "Moderate", the value of C1 is the same as result in D1
    (c) If B1 is "Mature", the value of C1 should multiply to 3
    If A1 is "Apples", it should multiply B1 to 5, regardless of B1 and C1's values
    If A1 is empty, C1 and D1 should be empty too

    I tried to modify the old formula, but it's confusing! Please help me!
    Thank You and God Bless!

    1. Hello!
      I recommend that you study the recommendations on how to use the AND function in the conditions of the IF function, which are given above in this article.

  38. Is it possible to do a formula like this. If the value of cell F65 is "Negligible" and the value of cell G65 is "Insgnificant" then cell F66 will be "Low". I have multiple conditions I would need to add to this formula along with what I provided above. For example if F65 is "Very Low" and G65 is "Minor" then F66 will be "Low" also if F65 is "Negligible" and G65 is "Minor" then F66 is "Low" could we add all of this to one formula and add more conditions if needed later on?

  39. Thank you for your page, it really helped me in some of the formulas I was looking for.
    I thank you for all your future assistance in advance and I will be keep in contact and.

  40. Is it possible to use an IF statement to sum columns? That is, if any cell in E3:E17 = Y, sum the values in their respective C3:C17 cells

  41. HI, I was just wondering if you could help me.
    What excel formula should I use to carry out this task:
    If B1 is in A:A then C1 to be 'True'

    Thank you so much

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

      =IF(COUNTIF(A:A,B1),TRUE,"")

      I hope this will help

  42. Hi,
    If i have 3 values of same number i.e A1=1,B1=1,C1=1, i need a result as "ok". Appreciate the help

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      Please try the following formula:

      =IF(AND(A1=1,B1=1,C1=1),"OK","")

  43. Hi,
    I am working on a commission sheet and would like to put a formula so that the user does not have to manually put the commission %. I have four ranges and 0 to 20 % discounts.
    So the criteria is:
    1) amount is less than equal to 50,000, disc is 0%, 4% commission,
    2) amount is greater than 50,000 and less than equal to 250,000, disc is 0%, 3% commission,
    3) amount is greater than 250,000 and less than equal to 500,000, disc is 0%, 2% commission,
    4) amount is greater than 500,000 1% commission.
    IF function works fine till I have 1 and 2, but once I add 3 It gives FALSE value, cant figure out the issue can anyone help.
    =IF(A550000,IF(A5250000,IF(A5<=50000,IF(B5,"0"%,2%))))))
    Thank U

  44. Hi,
    I am working on a commission sheet and would like to put a formula so that the user does not have to manually put the commission %. I have four ranges and 0 to 20 % discounts.
    So the criteria is:
    1) amount is 50,000 and 250,000 and 500,000 1% commission.
    IF function works fine till I have 1 and 2, but once I add 3 It gives FALSE value, cant figure out the issue can anyone help.
    =IF(A550000,IF(A5250000,IF(A5<=50000,IF(B5,"0"%,2%))))))
    Thank U

    1. Hello!
      If I understood your problem correctly you can use something like this formula

      =IF(A5<50000,0,IF(A5<250000,1,IF(A5<=500000,2,"")))

      Replace 0,1,2 with what you need

  45. Hello,
    I have the following situation:
    Column A - unique patient ID
    Column B - Diagnostics Service
    Column C - Therapy Service
    Patient IDs can repeat in Column A if they have received multiple services (Diagnostics and Therapy)
    I am trying to create formula:
    If patient ID has diagnostics AND therapy, then display 1
    If patient ID only has one service (either one), then display 0
    Thank you

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

      =IF(COUNTIFS($A$2:$A$5,A2,$B$2:$B$5,"Diagnostics") + COUNTIFS($A$2:$A$5,A2,$C$2:$C$5,"Therapy")=2,1,"")

      I hope this will help

  46. Hi Need help on an a if formula.
    Two partners (A&B) invest in a project; A puts in £80000 and B £Nil. At end of the project if there is a loss, A & B get NIL back. If its is a profit then;
    (i) Profits are split 50:50 till both A & B have £80000 profit allocated; and
    (ii) Once profits have been allocated as in (i), then all other profits get split between A and B as 40:60.
    I need to be able to show on excel so that I can change the profit figure to show various scenarios (e.g. if profit was £20000, £100000, £400000, etc)
    Thanks
    Sanjay

  47. Muchos Gracias! :)

  48. I am trying to right a formula that will do the following:
    If Cell B13 is 1 or 2 then (B13*1.1)
    If Cell B13 is 3 4 or 5 then (B13*1.05)
    If Cell B13 is 6,7,8 or 9 then (B13*1)
    If Cell B13 is 10,11,12,13,14 or 15 then (B15*.09)
    I've spent hours trying to figure this out any help would be appreciated, Thanks

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

      =IF(OR(B13=1,B13=2),B13*1.1,IF(OR(B13=3,B13=4,B13=5),B13*1.05,IF(B13<10,B13*1,IF(B13<16,B13*0.9, ) ) ) )

      You can change this formula and use the OR function in all conditions.

  49. I am a salesman trying to take when I have reached a certain number in sales that I reach a new bonus level. So say I sell between 14 to 16 cars then I get a bonus of $2500. How could I use an IF function and use a range for 14-16. Right now I have to do individual numbers like IF(C17=14,2500) then IF(C17=14.5,2500) and so on.

    1. Hello!
      Your condition for the sale of 14 to 16 cars can be written as a formula

      =IF(AND(C17<=16,C17>=14),2500,0)

      I hope it’ll be helpful.

      1. Thank you so much! It worked! I was doing something close but it wouldn’t calculate

  50. I have this problem:
    Column A have either "Apples" or "Oranges", I'll input a number on B, and result on C
    If A1 is "Apples", it should multiply B1 to 5, regardless of B1's value
    If A1 is "Oranges" --- If B1 is greater than or equal to 50, B1 should multiply to 3
    If B1 is less than 50, B1 should be subtracted by 10
    If A1 is empty, C should be empty too

    I did a lot of thinking, but can't get it right. Please help me!
    Thank You and God Bless!

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

      =IF(A1="Apples",B1*5, IF(A1="Oranges", IF(B1>=50,B1*3,B1-10), "") )

      1. Thank You Very Much for the answer! :)

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