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 109. Total comments: 4575

  1. Hi,
    Can you help figure out what formula i'm going to use? i want to get the sum of the whole column with the same equivalent in other column. for example a1 to A10 has different value let say 2,3,4,5,6 and in B1 to B10 has its equivalent value like 100,123,121,333 etc. i want to get the sum of row with 2 value in B column and its total sum.

    1. Hello Manny,

      If my understanding is correct, you need to sum only those cells in column B, that have 2 in the same row in column A. If so, you can use the following SUMIF formula:

      =SUMIF(A1:A10, 2, B1:B10)

  2. Hi
    Value in cell A3 is 10
    I want to check this value tn 4 Different Criteria
    1)Less than 20
    2)Greater than 20 but less than 50
    3)Greater than 50 but less than 80
    4)greater than 80
    Please help
    Regards
    Venkat

  3. Hello!

    I want to only bring back a value when column A shows "Project". I want to multiply 2 different columns together but the tricky part is that sometimes those fields are blank and it's returning a #Value! error which I would like to remove and show as a blank field.

    Column A: "Project" Verbiage needed
    Column B: Value 1 for multiplication (can be blank sometimes)
    Column C: Value 2 for multiplication (can be blank sometimes)

    If Column A = "Project", multiply column B x column C and if it returns a #Value error (from at least 1 field being blank), return an empty cell.

    Thoughts? Thank you in advance!

  4. Hello,
    I am having trouble displaying multiple matches from nested IF statements.
    As an example:
    I have a meal planner with QUICK, CASUAL, EXTRAVAGANT. QUICK = =15 but 30.
    Now say meal 1 takes 13-18 minutes, which puts it in Quick and Casual, but the validation stops after it meets the <15 logic, how can I get it to check the rest of the specifications to check the Casual block also?

    Thanks for any help.
    Sam

    1. Something didn't post right.
      Quick: first IF is less than or equal to 15, "QUICK" next IF equal or greater than 15 but less than equal to 30, "CASUAL", third IF equal to or greater than 30 "EXTRAVAGANT". The rest is correct as I put in a meal that takes 13-18 minutes, how to I have the formula block identify it as a Quick Casual Meal.

      Thanks

      1. Hi Sam,

        You can use the following nested If functions:

        =IF(A1>=30, "EXTRAVAGANT", IF(A1>=15, "CASUAL", "QUICK"))

  5. Hi,

    I need help combining 3 individual IF formulas into one. All work correctly as individual formulas but I can't figure out how to combine the 3 formulas into one. The individual formulas are as follows:

    =IF(OR(ISNUMBER(SEARCH("TAMPA BAY*",C2)),ISNUMBER(SEARCH("TAMPA TRIBUNE*",C2)),ISNUMBER(SEARCH("TAMPA BAY NEWS*",C2)),ISNUMBER(SEARCH("LEDGER*",C2)),ISNUMBER(SEARCH("NEIGHBORHOOD*",C2)),ISNUMBER(SEARCH("OBSERVER*",C2))),"PN","NO")

    =IF(OR(ISNUMBER(SEARCH("out*",C3)),ISNUMBER(SEARCH("CLEAR*", C3)),ISNUMBER(SEARCH("LAMAR*",C3))),"O","NO")

    =IF(OR(ISNUMBER(SEARCH("Bright*",C4)),ISNUMBER(SEARCH("Com*", C4)),ISNUMBER(SEARCH("Via*",C4))),"C","NO")

    Thanks for the help!
    Mary

  6. I have written this syntax:

    =IF($V$5100%,C$23=8/1/16,IF(AND(C$23>100%,C$23<=115%),$Q$5*1.75,0))))

    Why FALSE came up?

    Hope you could help.

    AM

  7. let me make a bit more clear
    I am a FOREX Currency Trader. I record all of my trades in a Trading Journal (excel file). I would like calculate the What is the Maximum Consecutive Winning or Loosing streak in number (don't want to add them together).

  8. If we have over 1000 rows & there are positive & negative values in one column. I want a formula to take out the maximum consecutive values (either positive or negative). I tried a lot but couldn't workout. Please help.

  9. Mark Sheet

    StudentName Tamil English Hindi Maths
    Arun 22 34 44 55
    Vinoth 45 56 76 43
    David 98 34 22 96
    Vasanth 35 87 45 69
    Andrew 78 89 98 74

    Result
    Student name-
    Subject name-
    Subject Mark-

    Note:
    1. Should Not use Vlooup
    2. If Student name and subject name change in Result , automatically subject score should display.
    3. The changes should done based on Mark Sheet given at above.

  10. Good day I need to write an if formula on tax brackets.

    If a person earns between 100 and 500 for example the tax will be X amount minus rebate of X amount. And if a person earns between 600 and 1000 the ta X will be a amount and the amount above X amount multiply with x% + a minus X amount rebate

  11. a b c d
    1 1 0 1
    1 0 1 1
    1 1 1 1
    0 1 0 0
    0 0 1 0
    0 0 0 0

    a, b and c are my conditions and d is my result so can I get the excel formula to get result for all six conditions.

  12. I seem to be going around in circles, I have two fields, one contains First Names and the other Surnames. Most surnames are single names but some are two names hyphenated and some are two names with a space between them.
    The end result I am looking for is for single surnames it will result in first character or first name followed by surname, no spaces.
    Both hyphenated and spaced dual surnames will be first character of firstname, followed by first character of first surname followed by last surname. So somebody with a name of Peter Barkley Smith for instance will result in pbsmith.

  13. I need help!! I am using the following formula:
    =SUMIFS('OE761-2'!C:C,'OE761-2'!B:B,"SGIANNA",'OE761-2'!E:E,"="&J33)
    Basically, I am summing the total in C:C if B:B has the name SGIANNA and the date in E:E is the same as the date in J33. It works just fine, but if the date in J33 is not found in E:E, I want to leave the cell blank. Any guidance would be much appreciated!

  14. Hi..............
    my question is???

    My Product Name - Samsung-E1200 just time price - 1120/-
    and then price drop it this product - Samsung-E1200 - 1175/-
    so sir howes condition apply i m not understand????

    so sir please suggest me............. and question is ????

    01-feb to 14-feb price 1120/-
    then 15-feb to 29-feb- price change 1175/-
    so suggest me???????

  15. Can anyone help me with this. I need an excel formula for the following. If there are 4 price ranges depending on volume sold, then what is the total revenue for a given volume sold?

    For example

    <10,000, price is 4
    10,000 - 20,000, price is 3
    20,000 - 30,000, price is 2
    More than 30,000, price is 1

    The formula should calculate the total revenues if I enter any volume number.

    Any help much appreciated.

  16. Dear Svetlana,

    I need a formula for the following. If there are 4 price ranges depending on volume sold, then what is the total revenue for a given volume sold?

    For example

    <10,000, price is 4
    10,000 - 20,000, price is 3
    20,000 - 30,000, price is 2
    More than 30,000, price is 1

    The formula should calculate the total revenues if I enter any volume number

  17. IF Cell1 <90 and Cell2 =180 and Cell2 values >=110 then return "Emergency"

  18. Hi, Ihave this conditions

    IF Cell1 <90 and Cell2 =180 and Cell2 values >=110 then return "Emergency"

    help meee

  19. How to calculate in excel if my ach% is 70% then score is 3 & value is 2175

    Ach % Score Value
    70% 3 2175
    85% 4 2900
    100% 5 3630

    kindly help me pls..

  20. Hi,
    I have an excel spreadsheet that have patients who either has only medical visits or medical and dental visits. Column A is patient name, column B is the type of visit. How do I identify if that patient only has one type of visit service, or if they have multiple type of services. Please help. Thank you very much

  21. Hello,

    Does anyone know how to use if and function on following arguments:

    If any two cells has false value then it should print previous argument as follow:
    If C24 = 4, D24 = 7% print PM, C24 = 15, D24 = 7% print LM. The following function is not working correctly:

    =IF(AND(C24<5,D24<5%),"PM",IF(AND(C24<10,D24<10%),"LM",IF(AND(C24<20,D24<20%),"RM","HM")))

    Thanks
    Meena

  22. hi,

    Does anyone has any idea what this formula means?

    it used to set CRM goals (data capture)

    =IF(BZ5="LO",BP5+0.1,IF(BZ5="MID",BP5+0.05,IF(BZ5="MOD",BP5,0.5)))

  23. Hi

    I have 4 cells in a row, 1 has a date in and the other 3 are blank.

    eg

    A1 - Blank
    B1 - Blank
    C1 - 01/04/2016
    D1 - Blank

    Therfore I want the answer in E1 with the date in.

    The next row may be

    A2 - 05/04/2016
    B2 - Blank
    B3 - Blank
    C$ - Blank

    I want column E1 to always give me the date from whichever column has a date in, can you help please? Ive tried alsorts of formulas which either come up with an error or TRUE.

    1. Hi KD,

      Please try the following formula:

      =IF(NOT(ISBLANK(A1)), A1, IF(NOT(ISBLANK(B1)), B1, IF(NOT(ISBLANK(C1)), C1,
      IF(NOT(ISBLANK(D1)), D1, "blank"))))

  24. Hi,
    I would like to know if you could advice me on this, I have two numbers in different cells, what I want is that, if these numbers are similar, on a third cell obtain the text "OK" and "Error" if they're different; the detail is that the data isn't precise, I mean one cell has 1.2 and the other one has 1.3, and they are similar but not equal, so what I want to know, is it possible to use an IF with a certain percent of tolerance?

    1. Hi Victor,

      I think you can use a formula similar to this, where 0.1 is the "tolerance":

      =IF(ABS(A1-B1)<=0.1, "OK", "Error")

      1. Yes thats the same method I used, thanks! it work perfectly!

  25. I have a document with multiple sheets. I need to use one for a "master" entry form to keep continuous record. A second sheet "Current" would record the most recent data entered pertaining to an exact unit. In this case lockers assigned to employees. When a locker is reassigned I would like the spreadsheet to transfer the data to the "Current" spreadsheet for an updated active list. Is this possible?

    1. Well you could use a VLOOKUP or if you don't edit the "Current" sheet you could manage it as a Pivottable instead of a separate SHEET you would only have to press the update on the pivottable.
      If this was helpful or if you want to know something you can always ask c:
      Hope this was useful.

  26. Hi,

    I am trying to do an if statement, but it doesn't seem to be working.

    It should indicate the following...

    Ignite 30 - $7.00
    Ignite 60 - $15.00
    Ignite 100 - $25.00
    Ignite 150 - $26.00
    Ignite 250 - $26.00
    Ignite 60 1S - $14.00
    Ignite 100 1S - $14.00
    Ignite 150 1S - $14.00
    Ignite 60 5S - $20.00
    Ignite 100 5S - $20.00
    Ignite 150 5S - $20.00
    Business Basic - $6.00
    Business VIP - $10.00
    Legacy to New - $3.00
    Upgrade - $3.00
    1S to 5S - $6.00
    IBLC Basic - $10.00
    IBLC Standard - $12.00
    IBLC Pro - $14.00
    Wireless Business Phone - $5.00

    Any help would be greatly appreciated.

    Can yo

  27. I need the formula please help me

    If a1=15,b1=9,"5500"....
    If a1=12 to 14, b1=8, "4000"
    If a1= 9 to 11, b1=7," 3300"
    If a1=5 to 8 ,b1=5 to 6, "1600"

  28. I am trying to build a nested function that includes the following conditions:

    IF I2 >0.3, then write "Focus on A"
    IF I2 <-0.3, then write "Focus on M" (here is -0.3 (minus 0.3))
    IF G+H<20 AND G<5 OR IF G+H<20 AND H<5, then write "OUT"
    If none of the conditions apply, write "non-specialist"

    I've created the following nested IF function, but I got an error message saying that I have too many conditions/terms on it. I would like to know how could I fix it:

    =IF(OR(AND((G2+H2)<20),G2<5), AND((G2+H2)<20,H20.3,"Focus on A",IF(I2<-0.3,"Focus on M", "Non-specialist")))

    I very appreciate you attention and help.

    cheers,

  29. Hello,

    I need to set the formula for the following,

    F3 column is having different percentage data. In want to grade the same as A,b & C.
    for ex : 0-70% = A, 70%-90% = B, 90%-100% = C
    Pl help,

    Thnx & regrds
    Sharma

  30. Manager of MKT = Rs 20000
    Manager of other department = Rs 18000
    Officer of MKT = Rs 15000
    Officer of other department = Rs 13000
    pls.tell me now

  31. Hi

    I am trying to get this formula to work but it keeps on coming up with #NAME when there is no data. If there is a “P” in either bb7 or bc7 then it works.

    Basically I am trying to work out whether a pupil has a unit pass. Not sure if you will be able to work out from the formula what I am looking for but here is the explanation:
    There must be a “P” in either BB7 or BC7 AND the following before a unit pass is awarded. (BK7 or bf7), (Bl7 or bg7), (bm7 or bh7).

    Here is the formula:

    =IF(BB7=”p”,”P”,AND(IF(OR(BK7=”p”,BF7=”p”),IF(OR(BL7=”p”,BG7=”p”),IF(OR(BM7=”P”,BH7=”P”),”P”,””)))))

    1. Sorry - Wrong formula above. This is the one.

      =IF(OR(BB8="p",BC8="p"),"P",AND(IF(OR(BK8="p",BF8="p"),IF(OR(BL8="p",BG8="p"),IF(OR(BM8="P",BH8="P"),"P","")))))

  32. Hello,

    i need to put the ending filter to IF function. How can i write the formula?

    Ex:- if this ending with "I" (AL-FRE-EXP-I), True = Invoice, False = Credit Note

    Thanks,
    Prabath

  33. 01/01/2015 0123 DEF Co High Risk
    01/01/2015 1234 ABC Co Medium Risk
    01/02/2015 1234 ABC Co Low Risk
    01/01/2015 5678 XYZ Co Low Risk
    01/02/2015 5678 XYZ Co Low Risk

    I need to get as a result (1) next to the company:
    -which doesn't appear the next month (such as DEF)
    -which is upgraded next month (such as ABC)
    And I need to get as a result (0) next to the company which is rated as Low Risk in every month.

    Is it possible?

    Thank you in advance

    1. Hi Elsa,

      To help you better, we need a sample table with your data in Excel and the result you want to get. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

  34. If I want to use the IF/OR function, but use multiple lines of text (ie, a column), can that be done?

    For example: =IF(OR(B3=O$2:O$360),"TRUE","FALSE")

    1. Hi RCL,

      If I understand you correctly you should use the following formula:
      =IF(COUNTIF(O$2:O$360, B3) > 0,"TRUE","FALSE")

  35. i am trying this formula but return by you enter more than no of nested permited by this formula is there any other way

    =IF(I37<50,VLOOKUP(C31,mainrates,7,0),IF(I37<100,VLOOKUP(C31,mainrates,8,0),IF(I37<250,VLOOKUP(C31,mainrates,9,0),IF(I37<500,VLOOKUP(C31,mainrates,11,0),IF(I37<1000,VLOOKUP(C31,mainrates,12,0),IF(I37<2000,VLOOKUP(C31,mainrates,13,0),IF(I37=5000,VLOOKUP(C31,mainrates,15,0)))))))))

    thanks

  36. i have 5 cell like:
    5
    4
    6
    7
    8
    10
    14
    when use if(a2<=5,3,if(a2<=8,5,if(a2<=10,8,if(a2<=14,10)))
    give me embty cell

  37. Trying to do a formula to calculate the following:
    Service Year is in Column A
    IF A>5 then (A-5)*2+5
    IF A<2 then "2"
    IF A is less than 5 but greater than 2 then = A

  38. Dear
    I have to use a formula that can divide the resultant column into three levels (Low, medium, and high). Suppose some values “X” is present in cell “C”. The formula should be like this
    If value in C is less than 50, result should be Low level
    If value in C is greater than and equal to 50 and less then 75, result should be Medium level
    If value in C is greater than and equal to 75, result should be Low level
    Please help me how can I develop this formula in excel?

    Thanks

    1. Hi Babar,

      Please try the following formula:
      =IF(C1<50, "Low", IF(AND(C1>=50, C1<75), "Medium", "High"))

  39. Hi,

    Need help solving formula issue below:

    Monthly Cost Plan Price
    Details Range Year 1
    Min 2 -25.00% -50.00% 18,256,000
    Min 1 -10.00% -24.99% 15,876,000
    Deadband 0.00% 9.99% 14,434,000
    Max 1 10.00% 24.99% 12,992,000
    Max 2 25.00% 50.00% 12,348,000

    My deadband number (limit) is constantly changing, e.g deadband 12 so
    if my delivery plan is 70 then it fell to range max 2 with price 12,348,000 ==> because max 2 unit range will be:
    12+(12*25)to 12+(12*50)

    How can I create an array formula with condition as follows:
    1. If delivery plan = 0, then monthly billing also 0
    3. If delivery plan > deadband, then monthly billing fell to which range and which monthly billing

    Hope it's not confusing. Thanks for your help.

  40. hi ,
    can you help me in excel . I have dispatch sheet but I want then I have enter in value in dispatch sheet and in another sheet dispatch sku's automatic less value from sheet .

  41. I need to run a function to search a table of datas.
    Eg. In Sheet 1, there are two columns A & B and as below:

    A B

    PIT- 3AR501/1 Pressure Transmitter
    PICA- 3AR501/1 Controller
    PIAL- 3AR501/1 Low Alarm
    PIAH- 3AR501/1 High Alarm
    PCV- 3AR501/1 Valve- Globe
    PIT- 3AR501/2 Pressure Transmitter
    PICA- 3AR501/2 Controller
    PIAL- 3AR501/2 Low Alarm

    In Sheet 2 I want to run a function in a cell suppose D1 to D100, that will search a specific value such as 'Pressure Transmitter' in entire column B of Sheet 1, and display the corresponding value of its column A. Likewise it will check the entire column B and give the corresponding values of column A, wherever it matches with 'Pressure Transmitter'.

  42. I am trying to create a formula for my spreadsheet with text. If there is an "a" in a cell, this equals 1 (a=1). And I want the total number of "a" in the whole column to total out so if there are 5 "a" = total of 5. Please help!

  43. I am attempting to create a column that will assign a number (1-5) dependent upon the combination of the contents of two separate cells. These cells contain either "NONE", "no", or "yes"

    =IF(AS2=”NONE”,5,IF(AND(AS2=”no”,AT2=”no”),0,IF(AND(AS2=”yes”,AT2=”no”),1,IF(AND(AS2=”no”,AT2=”yes”),3,IF(AND(AS2=”yes”,AT2=”yes”),4)))))

    As it stands I am receiving a #NAME? error.

    1. Did you ever receive feedback or know any more about this? I'm attempting something similar and I am struggling!!

  44. How to formula this:

    Deadline of submission is 10:00am but he is delayed in submitting the reports by 10:05 am so i want to get the difference in time also and in other column i want to appear the words advance or delay i want to be in this format:

    10:00am - 10:05 am = -5minutes(color red) = delay (color red) if on time or advance like this

    10:00am - 08:00 am = 2 hours (color green) = advance (color green)

    10:00am - 10:00 am = 0:00:00 (color green) = on time (color green)

    thank you

    1. Jan,
      Post #312 above is what you want to do with your formulas and it refers to what is called Conditional Formatting. In the above Post the cells are colored but in your case the text would be colored instead. Play around with the info but where it indicate "Fill" use the "Font" to change colors.
      I will play around with the formula using the time format but if anyone can also help you sooner with your formula above I can help with providing addition help on the conditional formatting of the text.

  45. Can I use this formula for text? I am trying to locate a specific word in a cell (in column A) and assign a value to that cell in column B. Here is my formula, but the error message says I have too many arguments entered. Please help!

    =IF(ISNUMBER(SEARCH("LABEL",A7)),3919.9,"",
    IF(ISNUMBER(SEARCH("CONTAINER",A7)),4901.10,"",
    IF(ISNUMBER(SEARCH("INSTRUCTIONS",A7)),4901.10,"",
    IF(ISNUMBER(SEARCH("TAGS",A7)),4821.10,"",
    IF(ISNUMBER(SEARCH("NAME PLATES",A7)),8310.00,"",)))))

    1. Remove the "", at the end of the first four lines, leaving it at the end of the last part - should work.

  46. hi, i am trying to write a formula for the following:
    80-100 , A
    65-79, B
    50-64, C
    <50,F
    but im not quite sure how to do that. can you please help me. thank you

  47. Hi thank you for these blogs, I noticed above in this formula, =IF((C2+D2)>=60, "Good", IF((C2+D2)>=>40, "Satisfactory", "Poor ")) there is an extra >
    If I could request your next lesson, it would be in arrays and tables within formulas. Thanks

  48. Hi,

    Can you help for following condition.,

    Model Slab
    Indica 1
    Indica 2
    MUV 1
    MUV 2

    using formula, if model indica, slab 1 means anws will be 100, if model indica, slab 2 means anws will be 200,if model MUV, slab 1 means anws will be 300, if model MUV, slab 2 means anws will be 400

    how write the above formula.,

  49. Hi

    I am trying to do an Excel IF Array function.

    I am trying to compare a country code, with that of a list of country codes, and let it return a specific value

    Here is my formula =IF(A1='Eu Countries'!$D$2:$D$29,"EU","Non-EU")

    It keeps returning False results, even though in my sample I can see countries and expect a True Value.

    Please could you help.

    Regards
    Chrystian Michalowski

  50. I am looking to only put 1 text if multiple values of a search is found in my search. Right now my formula will put multiple values in the output. The formula I am presently using is :

    =IF(ISNUMBER(SEARCH("TEXT1",B2)),"EXEMPT","") & IF(ISNUMBER(SEARCH("TEXT2",B2)),"EXEMPT","") & IF(ISNUMBER(SEARCH("TEXT3",B2)),"EXEMPT","") & IF(ISNUMBER(SEARCH("TEXT4",C2)),"EXEMPT","")

    If this matches 2 conditions it puts in EXEMPTEXEMPT

    What I would like to find is if any of the criteria matches write exempt.

    If (TEXT1 in B2) OR (TEXT2 in B2) OR (TEXT3 in B2) OR (TEXT4 in C2)then write EXEMPT otherwise leave blank

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