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 118. Total comments: 4573

  1. SUPER

  2. hello!

    I'm trying to create a formula wherein:

    Cell value needs to check if it is Stop, Pending and Others.
    Checks the Value in another cell it is pending or not then the condition in the table applies. wherein the cell value result will be the size.

    Size Category Stop Pending& Others
    X-Small <=1 <=1
    Small <=3 <=2
    Medium <=10 <=6
    Large <=20 20 > 15

  3. I'm trying to use a formula to return a value using multiple conditions as shown in this table:

    FE Gross PVR
    Units 1,450 1,500 1,550 1,600 1,650 1,700
    220 32 33 34 37 39 40
    210 31 32 33 36 38 39
    200 30 31 32 35 37 38
    190 26 27 29 32 33 34
    180 25 26 28 31 32 33
    170 24 25 27 30 31 32

    Example: If Units are >189 and less than 1499 and less than 1550, then return the value of 27.

    I would appreciate any assistance. Thank you!

  4. for some reason the formulas post here show different what the one i write
    =IF(A1>=37,10000,IF(A1>=28,1000),IF(A1>=19,100),IF(A1>=10,10),IF(A1>=1,1))

    1. Hi!

      You were almost there. You just have to close all IF's at the end of the formula, like this:

      =IF(A1>=37,10000,IF(A1>=28,1000,IF(A1>=19,100,IF(A1>=10,10,IF(A1>=1,1)))))

  5. i also use this
    =IF(AND(A1>=1,A1=10,A1=19,A1=28,A1=37),10000,A1)

    but it come back #VALUE

  6. I tried all your exaples for an issu i have but non of them comes with the correct answer

    i have a series o number for some score that need to be analyzed
    12 16 21 33 45
    i need to use a formula where is a number falls between a range of number to come back with another result this is my list

    1-9 1
    10-18 10
    19-27 100
    28-36 1000
    37-45 10000

    this is what I came up with
    =IF(AND(A1>=1, A1=10, A1=19, A1=28, A1=37),10000,A1)))))

    i also use this as a test
    =IF(A1<=37,10000, IF(A1<=36,1000,IF(A1<=27,100, IF(A1<=18,10,IF(A1<=9,1)))))
    but it only get th the first if statement

  7. I am trying to write an IF statement formula for the following condition:
    This is TEXT only, no numbers.

    If Column F is blank, import the text value from Column K (same row)
    If Column F is not blank, leave it as-is.

  8. Hi,

    My data reads:

    Status Category
    Successful Normal Minor.Production
    Successful Normal Minor.Production
    Successful Normal Minor.Production
    Successful Standard-Minor
    Successful Standard-Minor
    Successful Normal Minor.Production

    I want to compare both the columns for text and return a different text eg: "Successful Minor". I used this statement =IF(AND(C3 = "Successful", E3 = "Minor"), "Minor Successful", ) but this means the column B needs to specify minor. How do I do text search? Hope my question is clear. Appreciate your assistance.

    Thank you kindly

    Regards,
    -V-

  9. Hi .. I am trying to set a formula to calculate a hotel room rate based on the day of week. If Fri, Sat, Sun then $230 and if Mon-Thu $240. Person needs to enter arrival date 1/8/2016 .. then this reports back Fri (=c6 with the custom formatting of ddd)

    Ex
    Cell C6 has 1/8/2016, then C5 is set to =c6 (custom formatting of ddd). The formula I have is IF(C5="FRI","$230","$240").
    Problem is C5 does not read FRI and is shows, but reads as =C6 and $240 is returned.
    Any help in getting the formula to recognize day of week that is returned off a formula.
    Thank you

  10. I need help!

    Conditions:

    If sales are greater or equal to 10 then multiply the named cell RateA by Cost
    If sales are greater or equal to 20 then multiply the named cell RateB by Cost
    If sales are greater or equal to 30 then multiply the named cell RateC by Cost

  11. Hello and please help.
    Take Row A through L
    If A1 equals K1 then L1 needs to move to B1
    How to formulate this
    =If(A1=K1;L1=B1) ?????

    1. Hi Luke,

      Here's the formula for B1:

      =If(A1=K1, L1, "")

      The formula will copy a value of L1 to B1 if A1=K1.

  12. Sorry something went wrong ... below the Table again ...

    I need a formula (If I guess) create a new Score from the NOT PASS (less than 50) an individually new PASS Score in another cell. I tried many things but failed ... I think it should be like ... The difference from PASS Score to NOT PASS Score plus some individually value added from the difference.

    Ehm is ... Thank you all for help and understanding my bad English. I can explain it in German, but this is an english writing/reading forum. Smile

    U1 U2 Part Read completed Write Point Score
    79 87 10 10 10 10 10 100 50
    78 99 6 7 8 6 5 57 55
    78 95 5 5 8 6 5 50 58
    76 77 5 3 8 8 5 12 16
    79 89 6 3 8 4 8 23 35
    62 19 6 2 5 5 5 19 26
    71 14 5 4 8 L 5 53 45
    73 49 4 3 8 6 7 32 31
    34 9 5 5 8 5 5 27 23
    77 87 5 7 8 5 5 38 29
    71 17 5 2 5 4 8 9 18

    1. Sorry ... I cant figure it out ... what happen to the table in my post. I formated in Courier ...

  13. Hello ... I need a formula which counts the difference between the pass score >50 and the not pass score <50 and creates an individual pass score depending on the difference from the no pass score to the pass score.

    Sorry my english ... I guess it will be an multiple IF and Formula ...

    Thank you for advice and help

    U1 U2 Part Read completed Write Point Sco
    79 87 10 10 10 10 10 100 100 50
    78 99 6 7 8 6 5 57 55
    78 95 5 5 8 6 5 50 49 58
    76 77 5 3 8 8 5 12 16 25
    79 89 6 3 8 4 8 23 26 35
    62 19 6 2 5 5 5 19 17 26
    0 9
    71 14 5 4 8 L 5 53 45 54
    73 49 4 3 8 6 7 32 31 40
    34 9 5 5 8 5 5 27 23 32
    77 87 5 7 8 5 5 38 39 48
    71 17 5 2 5 4 8 9 9 18

  14. How can I put the formula for below case:

    If "A" Column has various values in different rows like below,
    A1 is 12000
    A2 is 20000
    A3 is 25000
    A4 is 30000
    A5 is 35000
    A6 is 2043
    A7 is 1916

    I got the result like this
    =IF(AND(A1>=12000,A1=2043,E3188<=2043), "SL", "others")

    But i want to put it in one formula for whole rows

  15. Hi Shiraz,

    Do you mean if A1 is equal to or greater than 40, the profit will be 4% or $20 whichever is higher?

    Anyway, you can use a formula similar to this:

    =IF(A1>=40, MAX(A1*0.04, 20), MAX(A1*0.04, 15))

    1. ill repeat again..if A1 is 20 , the profit will be 4% of $15 which ever is higher, and if A1 is 40 the profit is 4% or $20 which ever is higher....these are 2 different condition have to apply on cell..hope you understood.

      1. just read A1 20 as a & A1 40 as b,

        if A1 is (a) the profit will b 4% or $15 and if A1 is (b) the profit will b 4% of $20...

        1. Shiraz,

          I understand your conditions. But what if A1=1, or A1=21 or A1=50. How is the profit calculated? Or can A1 contain only 2 values (20 and 40)?

          1. we have only two products 20 & 40 or you can read as apple & grapes...
            for apple let us assume the amount $400 & grapes $600 , now i have to apply a formula to single cel " if A1 is apple then the profit should be 4% or $15 which ever is higher and if A1 is grapes then profit should b 4% or $20 which ever is higher " i hope you got it.

            1. Assuming that A1 is either "apple" or "grapes" and B1 is amount ($), the formula is as follows:

              =IF(A1="apple",MAX(B1*0.04,15),IF(A1="grapes",MAX(B1*0.04,20), ""))

              1. thanks :)

  16. hi, i need on formula for below condition
    if A1 is 20 the profit will b 4% or $15 whichever is higher and if A1 is 40 the profit will b 4% of $20 whichever is higher.

  17. I need a formula where I say if A2 (which is a persons name) is equal to a name in column B then bring me the information that it shows under this persons name in column D. How may I do this formula?

    1. Hi Lysbeth,

      I think you'd better use the VLOOKUP function for this task. A formula can be similar to this:

      =VLOOKUP(A2,B2:D100, 3, FALSE)

  18. hi svetlana, i'm having a problem figuring out how to put this in formula

    G8 is equal to (46 to 35) is "NI"
    G8 is equal to (34 to 23) is "L"
    G8 is equal to (22 to 11) is "M"
    G8 is equal to (10 to 0) is "H"

  19. Hello.

    I need to formula to evaluate a string, for example:

    AB12345
    CD34567
    EF5678
    G6789

    I have 4 models ( AB CD EF G), and each model has 2 types, Summer and Winter.
    Numbers between 1000 1999, 4000 4999 and 6000 6999, classified as Summer. Numbers between 3000 to 3999, 5000 to 5999 and 9000 to 9999 classified as Winter.
    I'd like a formula to classify them fast, instead of analising them manually or using procvs, because i have a lot of them, and it gets very slow when i use filters, due to procv formulas.

    Like this:

    AB12345 "Summer AB"
    CD34567 "Winter CD"
    EF5678 "Winter EF"
    G6789 "Summer G"

    Maintain the letter/letters ( it's 1 letter or 2, depends ), and classify the numbers.

    It it possible ?

    Can someone help me or point me in the right direction please ?

    Thank you !

    1. Unable to help me ?

  20. Hi, I am trying to automate a response according to 2 conditions (Likelihood and Consequence in a Risk Matrix). The Matrix provides for 25 scenarios (eg, Likelihood has 5 - Rare, Unlikely, Possible, Likely, Almost Certain. Consequence has 5 - Minimal, Minor, Moderate, Significant, Severe). There is a Likelihood column and a consequence column and a 3rd column which is the a level of risk which is the result of the 25 combinations of the other 2 columns. I thought of using "IF((AND formula but I understand this can only do up to 7 scenarios. Is there anlther formula I can uses?

  21. Hello,

    =IF(V4>=27, "Excellent", IF(V4>=19, "Good", IF(V4>=9, "Satisfactory", "Poor "))))
    Can you help me regrading this formula because ist not working when try with Excel file , I have value (V4) in my file from 12 to 28,
    When I try in single mode formula working properly,

  22. Hi. I am making a simple formula for tests results. I have already a list for the percentage of the raw scores. All I wanna do is when I input the raw score (1st column) its equivalent percentage will automatically show in the next column. :)

  23. Hi,
    I am having trouble combining the following rules:
    1. If A:A ="Amy" AND B:B contains "Show", "Show"
    2. If A:A ="Amy" AND B:B contains "Dress", "Dress"
    3. If A:A does not equal "Amy", "other"

    Thanks

    1. Hi rue,

      If you want to return a value from column B if column A contains "Amy", "other" otherwise, use the following formula:
      =IF(A1="Amy", B1, "other")

      If you need a formula for 2 conditions, "amy" in A and "show" / "dress" in B, use the following one:

      =IF(AND(A1="amy",OR(B1="show", B1="dress")), B1,"other")

  24. Hello, new here. Please help me.

    Amount of Net Taxable Income Rate
    over but not over
    10,000.00 5%
    10,000.00 30,000.00 500+ 10% of the excess over 10,000
    30,000.00 70,000.00 2,500+15% of the excess over 30,000
    70,000.00 140,000.00 8,500 +20% of the exces over 70,000
    140,000.00 250,000.00 22,500+25 % of the excess over 140,000
    250,000.00 500,000.00 50,000 +30% of the excess over 250,000
    500,000.00 125,000 + 32% of the excess over 500,000

    based on above tax table i would like to come up with a formula to find the tax due of the following taxable income:

    1,070,018.83
    277,667.62
    400,066.46
    191,497.18
    877,016.35
    504,028.75
    116,979.02
    748,277.72

    Thank you in advance to those who can help.

  25. Hi Svetlana,
    I am unable to figure this out.
    I need a formula that says: If any of the cells Barcode:Barcode contain the term "1 In Production", then add the text "In Production" to corresponding "1 In Production" cell.

    Many Thanks

  26. I have this formula
    If((AND(S1="CALIFORNIAN", T1=0.5, EXACT(UPPER(X1),X1), LEN(X1)>15)), TRUE, FALSE

    This formula works good if all characters are upper case. But I want this formula to also do this, if it is not all upper case then change length to >17. So if my text is Proper the text string can be longer.

    Thanks for your help

  27. I need a formula!!!
    can you please help me on this....

    I have 4 cells with dates
    If I enter date in first cell then value should be equal to 10%,
    If I enter date in first and Second cells then value should be equal to 30%,
    If I enter date in first and Second and third cells then value should be equal to 50%,
    If I enter date in first and Second and third and fourth cells then value should be equal to 100%.

    Pls help me

  28. Hi im trying that

    if input is >100000 and 200000 to < 300000 to be consider as 2 points

    like this N number of times.

    Means each lack to be consider as 1 point, how to make a formula in excel

  29. Need to create a formula that works for the data below. Please help.
    6.50 = Diamond

    Thanks in advance.

  30. Hi Svetlana,

    You have helped me before with Excel and I need your help again.

    I am developing a spreadsheet with details of research studies. I have a comments column which I want to fill in as "Closed" if the study end date in Column N has passed. At the same time for some studies I don't have study end date so unless the date is passed it is showing false whereas I want it to be blank if column N has no date or if teh study hasn't closed yet. Formula I am using is
    =IF(AND(N18"",N180),IF((TODAY()-N18)>0,"Closed",IF((TODAY()-N18)<0,"")))

    Many thanks for your help

    BW

    Jamil

  31. Hi,

    I need help with a formula

    my date condition 01 Oct 2015

    If date >= 01 Oct 2015, I need to meet A1 cell value >=70 to equal 70 & <=(10) to equal (10), otherwise A1

    If date <= 01 Oct 2015, my answer is A1

  32. Hi,

    I need help with a formula.

    I want to return the following values;

    If Cell Value is less than 40 = 10
    If Cell Value is between 40 & 49 = 6
    If Cell Value is between 50 & 59 = 4
    If Cell Value is between 60 & 64 = 2
    If Cell Value is greater than or equal to 65 = 1

    I've tried using the formula below but it only seems to return either the numbers 10 or 6.

    =IF(C2=40,"6",IF(C2>=50,"4",IF(C2>=60,"2",IF(C2>=65,"1")))))

    1. I have the same problem, how did you fix it?

    2. I figured out why the formula wasn't working, i've managed to sort it out.
      Thanks

  33. if any of two cells out of 3 cells are "false"(row wise)..then the third cell value should be in the "Result" column.

  34. I want help with a formula,

    I have 3 columns for audit, if all three columns says Yes then i want the finding to return as a smile.

    if any of the columns have a NO then the finding to return as Non Compliant..

    can you please help

  35. Hi Svetlana,

    Could you please help me to figure out the formula, which will find identical cells with identical ID, Instrument, Amount, but different Type and will return "hedge"

    ID Instrument Type Amount
    1212 XAUUSD Buy 30
    1234 CL Buy 1000
    1255 XAUUSD Buy 40
    1255 APPLE Buy 150
    1255 XAUUSD Buy 20
    1255 XAUUSD Sell 20

    Thank you ahead!

    1. apologies for the format appeared above.
      However, for the case above the last two rows should appear as "hedge"

  36. Hi,

    IF 1 to 749 should count as 1 and in case if its blank it should not calculate or it should show as 0)

    please help

  37. I guess non costumers are not welcome...

    1. customers*

  38. Hi Svetlana cheusheva.......
    how to use if condition in my ques......
    plz tell me....
    * display sales order after 2-jun,product name beginning with letter "G"and unit sold in excess 100.
    * display all details ; sales date on 1-jan or 3-jan and number of unit sold less then 150.
    * list those records sales date and 2-jan ; unit sold less then 150 and product name ending in letter "est".
    * display all records for countries in state of Florida with words north or south in country name and land area are more than 500.
    * display those records for countries in the state of California or Colorado with population between 200000 and 300000 and having unit of more than 100,000.

  39. I have 3 columns, Lets say A1, B1, and C1. I need to make a concatenate function that:

    If A1="*" and B1=(random text) then C1 = B1
    If B1="" and A1=(random text) then C1 = A1
    If A1"*" and B1"" then =CONCATENATE(A1,".",B1)

    1. Hi Austin,

      Does A1"*" mean any text in A1? And most likely B1"" is supposed to be <>""(non-empty), right?

      Anyway, you can use a nested IF formula similar to the below one, just adjust the criteria according to your needs:

      =IF(AND(ISTEXT(A1), B1="text"), B1, IF(AND(A1="text", B1=""), A1, IF(AND(ISTEXT(A1), B1<>""), CONCATENATE(A1,".",B1), "")))

  40. =IF(I11=26,"10000",IF(I11=27,"15000",IF(I11=28,"20000")))-E8

    I11 its +1 and value is +5000
    is there shorter way to do this?

  41. I was wondering if you could provide assistance with a formula I am trying to figure out.
    I need to look at a range of cells and determine if any of them have been the text "Ready" in them. IF they have the word Ready in them, then I need a different field to have a date in it. Is there a formula that can assist with this?

    1. Hi Jayne,

      You can use a formula similar to this:

      =IF(A1="Ready", TOADY())

      The formula will insert the current date if A1 contains "Ready". And you can replace TODAY() with any other date that you need.

  42. Hello,
    Which formula can I use if I want to return a cell value using a If clause. I am looking for something like " IF A1=OT Print value from B1.

    1. Hello Vinay,

      Here you go: =IF(A1="OT", B1, "")

  43. I need column A to reflect what is in column B. If B1=816114 I need A to say "SMC Main". If B=816164 I need A to say "CBRE". If B=811739 I need A to say "Ballard". If B says ANY other number, I need A to be blank (NOT say false). I am having trouble with that last part.

    1. Hi Brit,

      Try the following nested IF's:

      =IF(B1=816114, "SMC Main", IF(B1=816164, "CBRE", IF(B1=811739, "Ballard", "")))

      1. Thank you so much! worked great!

  44. Hi Svetlana,

    I am trying to obtain the most recent date from four different cells and want the data to return first if the most recent date is in cell AM4, second if the most recent date is in cell BH4, third if the most recent date is in cell CC4 and Fourth if the most recent date is in cell CX4. I am currently using the below formula which doesn't work if the most recent date is in cell CX4 and should return Fourth. Can you assist please?

    =IF(AND(AM4>BH4,AM4>CC4),"FIRST",IF(AND(BH4>AM4,BH4>CC4),"SECOND",IF(AND(CC4>AM4,CC4>BH4),"THIRD",IF(AND(CX4>AM4,CX4>BH4>CC4),"FOURTH",""))))

    1. I have now done this. Thanks

      =IF(AND(AM3>BH3,AM3>CC3,AM3>CX3),"FIRST",IF(AND(BH3>AM3,BH3>CC3,BH3>CX3),"SECOND",IF(AND(CC3>AM3,CC3>BH3,CC3>CX3),"THIRD",IF(AND(CX3>AM3,CX3>BH3,CX3>CC3),"FOURTH",""))))

  45. Very helpful article, thanks.

    I have a question though...

    I want a formula to return one of, say, 3 results based on the contents of a (source) cell on the same row but only if one specific string is present in that source cell and the result is defined by another specific string within the source cell.

    EG the source cell has "3m long Rubber cable" in it. The cables can only be 3m, 5m or 10m long but they can also be 'PVC' and 'High Temperature' types. So, in the example above, the formaula in the 'result' cell should return '3' because the word 'Rubber' is present in the source cell, and 3m is the length of the cable.

    I had been trying to use the 'IF(ISNUMBER(FIND("abc",[source cell reference])), [result],) formula which will work with half of my problem but how to combine it with more to pick not only 2 strings but also multiple alternatives is beyond me.

    Hope that's clear. Regards, Martin Winlow.

  46. Hello I am working on a spreadsheet and need help with a formula.

    If today's date is between the dates in C1 (8/31) and E1(9/7) I want it to display the data in cell D3, if todays date is between the dates in E1 (9/7) and G1 (9/14) then display the data in Cell (F3). Can someone please help me?

    Thank you in advance

  47. Please help me come up with a formulae to do the both the following in one go

    there are 6 Subjects , I need to give grades for average marks above 90,80,70,60,50,35 and below 35 A,B,C,D,E, Pass and Fail respectively. Also if single subjects are lesser than 35 then it should be fail

    please let me know the if function for the above

  48. Hi,
    Below condition is not working
    IF(OR(A1=C1,B1*D1),IF(A1=C2,B2*D2),IF(A1=C3,B3*D3))

    Column A Column B Column C Column D
    B81234 16 B91456 $8,995.00
    B81345 19 B81234 $4,887.50
    B91456 27 B81345 $5,391.00

    can anyone help me
    Manish

    1. Hi Manish,

      I believe the correct syntax is as follows:

      =IF(A1=C1, B1*D1, IF(A1=C2, B2*D2, IF(A1=C3, B3*D3, "")))

  49. Good Morning, I'm trying to achieve the following:

    IF column "FIRST NAME" OR column "SECOND NAME" contains "Ltd" OR "Plc" THEN Message "company details must be entered in the "TITLE" column".

    Can you help please?

  50. Hiya,

    Please help me come up with a formulae to do the both the following in one go

    PQ63483C to become PQ63483
    CRD63483C to become CRD63483

    In essence just to copy but remove the final C off of any data put in the A Column

    I keep experimenting and I can't seem to work out a conditional formulae that works for both

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