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 11. Total comments: 2544

  1. I'm trying to get two texts with the same number value...
    home=0.5
    office=0.5
    any other entry=0

    any thoughts on how to do that?

    =IF(B5="home"or"office", 0.5,0) didn't work

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

      =IF(OR(B5="home",B5="office"), 0.5,0)

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

    2. Hello!
      Please try the following formula:

      =IF(OR(B5="home",B5="office"),0.5,0)

      I hope it’ll be helpful.

  2. I need to return 100% in a cell for cells B2/A2. If B2 = 0, and A2= 0, I want the result to be 100% as a goal attainment returning 100% in C2.

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

      =IF(AND(B2=0,A2=0),100%,B2/A2)

      I hope it’ll be helpful.

  3. Hi,
    I am trying to create a formula with 3 different conditions.
    If J3<5 and K3<5 it should calculate K3-J3+1
    If J34 it should calculate 4-J3+1
    If neither is true the result is 0

    I have tried different variations o the following but nothing works:
    @IF(OR(AND(J3<5,K3<5),k3-j3+1,AND(J34),=4-j3+1,0))

    Any advice would be greatly appreciated. Thank you

    1. ERROR in 2nd "If" condition I posted.
      Should read: If J34, it should calculate 4-J3+1
      ERROR in formula I posted.
      Should read: @IF(OR(AND(J3<5,K3<5),=k3-j3+1,AND(J34),=4-j3+1,"0"))

      I copied and pasted so I am not sure what happened. So sorry for any confusion!

      1. OK so strange - after hitting Send the text changes in my post!
        Where it says J34 it should say IF J3 is less than 5 and K3>4.
        Hope the 3rd time is a charm! Thanks again.

        1. NEVER MIND - I figured out by studying at least 1,000 questions already answered here over the past few days.

          Correct formula: =IF(AND(J3<=4,K3<=4),K3-J3+1,IF(AND(J34),4-J3+1,0))

          Thanks to everyone who answers strangers very complex problems on this site. AMAZING!

          1. And once again my formula changed upon posting.

            If anyone needs the correct formula - respond to this and I will try to write it again with some other method

  4. Hello,

    I'm working on a project with 3 criteria and my formula seems to super long.

    =IF(AND(K2=12,L2>2),6,IF(AND(K2=6,L2>2,L28,M211,M20,M22,L20,M25,L210,M28,M26,L20),2,IF(AND(K2=3,L2=8,M2>25),2,IF(AND(K2=6,L2=8,M2>25),4,IF(AND(K2=6,L2=8,M2>25),4,0)))))))))))))

    1. Column K is fixed with 3 numbers = 3, 6 and 12
      Column L could be between from 1-12
      Column M could be between from 1-31

      This is my formula so far. It's Super long. Maybe you can help me.

      =IF(AND(K2=12,L2>2),6,IF(AND(K2=6,L2>2,L28,M211,M20,M22,L20,M25,L210,M28,M26,L20),2,IF(AND(B2="KSA",K2=3,L2>2,L26,L28,M28,M20,M212,M22,L25,L28,M28,M20,M22,L22,L22,L22,L22,L22,L22,L225),2,IF(AND(B2="UAE",K2=6,L2=8,M2>25),4,IF(AND(B2="KSA",K2=3,L2=8,M2>25),2,IF(AND(B2="KSA",K2=6,L2=8,M2>25),4,IF(AND(B2="UAE",K2=6,L2=8,M2>25),4,IF(AND(B2="Jordan",K2=6,L2=8,M2>11,M225),4,IF(AND(B2="Jordan",K2=4,L2=8,M2>11),2,0))))))))))))))))))))))))))))))))))))

  5. Hi all looking for help with and IF AND and Or function

    im trying to get it to either times by .25 if both parameters are met or if only one then times by .5

    And if none of the parameters are met then leave as is.

    =IF(AND(J45="Tigress",M45>100000),O45*0.25,O45)*(OR(IF(J45="Tigress",O45*0.5,O45)))

    Any help would be much appreciated

    1. =IF(AND(J45="Tigress",M45>100000),O45*0.25,IF(OR(J45="Tigress",M45>100000),O45*0.50,0))

      This should work

      Explanation on the below

      If J45=Tigress and M45 less than 100000, value in cell O45 will multiply to 0.25
      If J45=Tigress or M45 is less than 100000, value in cell O45 will multiply to 0.50
      If neither of the equation, the answer will be 0 - or you can change it to "Not found" instead of 0

  6. Hi All,
    Need a help in sorting out a problem. I'm trying to use the combination of IFS, AND, OR here.
    (All values are dates)

    Condition 1:
    Today's date (C1) = 04-12-20
    A1 = 04-12-19, 04-12-18
    &
    A1 = 04-02-21, 04-12-21

    I want to merge these conditions into a single cell E2

    If (C1-A1365,"Past 13+ Months") and if(A1-C190,"Fcst 4+ Months")

    Problem is that, it is reading two conditions as same, if the value is 0, which I'm trying to avoid.

    I would really appreciate some help on this by anyone.

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

      =IF((C1-A1) > 365,"Past 13+ Months",IF((A1-C1) > 90,"Fcst 4+ Months",""))

      I hope it’ll be helpful.

  7. I am preparing examination results, that consist of thirteen subjects each subject has two scores, then i calculated the average for each subject by considering two score i need a help on how to find the result points by considering seven subjects that a student got higher marks.

  8. Hello
    Rate Quantity Result
    2 2 4
    2 Text Text
    Text 2 2
    Text1 Text2 Text2

    I need the result like this help me with the formula please

    1. 2*2 = 4
      2*Text = Text
      Text*2 = 2
      Text1*Text2 = Text2

      All possibilities should be in one formula

  9. Hi! A non-techy person here and I need help for the formula for this please. (If B2 and C2 is blank then 0, if B2 and C2 has a value then D1+B2-C2) Thanks in advance!

    1. Hi,
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Please use the following formula

      =IF(AND(B2="",C2=""),0,D1+B2-C2)
      or
      =IF(ISBLANK(B2:C2),0,D1+B2-C2)

      I hope it’ll be helpful.

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

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

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

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

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

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

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

  17. Hi Friends

    Could you please help me to get the excel formula?

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

    Thanks
    Manik

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

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

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

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

      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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    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.

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

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

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

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

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

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

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

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

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

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

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

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

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