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 23. Total comments: 4553

  1. I'm trying to get an if function to cooperate with me.

    if(a17=12, then c17=c16). so basically if there is the number 12 is cell a17 then cell c17 will equal c16

  2. tomorrow is my assignment test so do you send me dum excel file to practice all advance formula i.e, multiplr if,sumifs,index,power query,time and dates subtraction,pivot table...and more,,,
    which will help me to build my confidence....please help me ???

  3. I'm attempting to do a nested If/or to populate cells based on text contents of another cell, regardless of case of the words in the source cell.
    Basic summary: If A1 contains the word "Normal" or "normal" in the text, A2 = "Normal"
    If "abnormal" or "Abnormal", A2 = "Abnormal"
    If "no call" or "No Call", A2 = "No Call"

    What I have currently is below, but I can't find the error.

    =IF(OR(((ISNUMBER(SEARCH("abnormal",a3)),"Abnormal"),ISNUMBER(SEARCH("no call",a3)),"No Call"),ISNUMBER(SEARCH("normal",a3)),"Normal"))

    1. Hello!
      Please re-check the article above since it covers your case.

      =IF(ISNUMBER(SEARCH("abnormal",A3)),"Abnormal", IF(ISNUMBER(SEARCH("no call",A3)),"No Call", IF(ISNUMBER(SEARCH("normal",A3)),"Normal","")))

      1. Thank you for your help! I hadn't realized I was missing that set of quotation marks. I overcomplicated it for what I needed, it seems.

  4. I am trying to create an IF/AND Statement with 2 criteria. The criteria are below. Each point value needs to have both the start and spread value achieved to hit that point value.

    Point Value Starts/Spread

    24 8 / $3,300
    18 7 / $2,900
    12 6 / $2,500
    6 5 / $2,100

  5. I have a formula I am trying to come up with and have multiple ranges of numbers involved.

    Is converting the numbers below into a formula so when I enter my blood pressure readings into my data sheet so the status column changes to the appropriate status when the following conditions are met for each status.

    NORMAL less than 120 and less than 80
    ELEVATED between 120 129 and less than 80
    HYPERTENSION STAGE 1 between 130 139 or between 80 89
    HYPERTENSION STAGE 2 140 or Higher 140 or 90 or Higher 90
    HYPERTENSIVE CRYSIS Higher than 180 180 and/or Higher than 120 120

    For example when I enter 118/75 the status changes to Normal
    138/65 the status changes to HYPERTENSION STAGE 1

    1. Hello!
      Use substring functions in Excel to extract numbers from text:

      =IF(AND(--LEFT(A1,SEARCH("/",A1)-1) < 120,--MID(A1,SEARCH("/",A1)+1,10) < 80),"Normal",
      IF(AND(--LEFT(A1,SEARCH("/",A1)-1) > = 120,--LEFT(A1,SEARCH("/",A1)-1) < = 129,--MID(A1,SEARCH("/",A1)+1,10) < 80),"ELEVATED",
      IF(AND(--LEFT(A1,SEARCH("/",A1)-1) >=130,--LEFT(A1,SEARCH("/",A1)-1) < = 139, --MID(A1,SEARCH("/",A1)+1,10) > = 80,--MID(A1,SEARCH("/",A1)+1,10) < = 89),"HYPERTENSION STAGE 1",
      IF(AND(--LEFT(A1,SEARCH("/",A1)-1) > = 140,--LEFT(A1,SEARCH("/",A1)-1) < = 179, --MID(A1,SEARCH("/",A1)+1,10) > = 90,--MID(A1,SEARCH("/",A1)+1,10) < = 119),"HYPERTENSION STAGE 2",
      IF(AND(--LEFT(A1,SEARCH("/",A1)-1) > = 180,--MID(A1,SEARCH("/",A1)+1,10) > = 120),"HYPERTENSIVE CRYSIS","")))))

      This should solve your task.

      1. The help in the formula is appreciated, but I am still not getting the result I needed.

        The table I have to record my data has 8 columns on it and uses columns B-I the data starts at B6 to I6.

        I want to use the following information to use as the criteria in a formula for when I enter my BP Data into (D) and (E), (I) Changes status based on the data entered into (D) and (E).

        NORMAL less than 120 and less than 80
        ELEVATED between 120 129 and less than 80
        HYPERTENSION STAGE 1 between 130 139 or between 80 89
        HYPERTENSION STAGE 2 140 or Higher 140 or 90 or Higher 90
        HYPERTENSIVE CRYSIS Higher than 180 180 and/or Higher than 120 120

        B C D E F G H I Date Weight Systolic Diastolic Pulse BMI Weight Status BP Status

        What I need is a formula that uses the information above for when you put your BP data into column D and column E, column I changes the status to Normal, Elevated, HYPERTENSION STAGE 1, HYPERTENSION STAGE 2, or HYPERTENSIVE CRYSIS based on the data numbers put in Column D and Column E.

        For example:

        If you put in 119 in (D) and 79 in (E), (I) Would have Normal as status
        If you put in 119 in (D) and 85 in (E), (I) would read Elevated Status
        and so on....

        1. Hi!
          The formula doesn’t work since it has been created based on the details you provided in your first query. I see from your subsequent comment that your task differs from the one you originally described. Time was wasted. I think that using this formula and the recommendations of the article above, you can find the solution you need.

          1. Based on my latter inquiry what would the formula be? I really need help with this. Unable to come up with the entire formula on my own.

  6. Thank you for this, it really helped me a lot on my excel in OPERATIONA MANAGEMENT AND TQM if some may ask you could also do this.
    =IF(OR(AND([@Item]="Pencil",[@Units]>9,[@Total]>1000,[@Region]="Central"),AND([@Item]="Binder",[@Units]>3,[@Total]>1000,[@Region]="Central"),AND([@Item]="Pen",[@Units]>4,[@Total]>1000,[@Region]="Central"),AND([@Item]="Desk",[@Units]>1,[@Total]>1000,[@Region]="Central"),AND([@Item]="Pen Set",[@Units]>2,[@Total]>1000,[@Region]="Central")),"Yes","No")

    or

    =IF(AND([@Item]="Desk",[@Units]>1),"5%",IF(OR(AND([@Item]="Pencil",[@Units]>9),AND([@Item]="Binder",[@Units]>3),AND([@Item]="Pen",[@Units]>4),AND([@Item]="Pen Set",[@Units]>2)),"2%","0%"))

    "as an example"

  7. Hi. I am working on a file that looks like this.

    Date submitted Quarter Deadline Remarks
    9/7/2021 4th 9/01/2021 Late/On-time

    The result that I wanted is like this:

    a. If the time of submission is beyond three days after the date reference (deadline with 3-day extension), remarks should be LATE. Excluding weekends from the 3-day extension.
    Example, deadline is on 9/1/2021, output is submitted on 9/7/2021, remark is LATE.

    b. If submitted within the deadline with 3-day extension, remarks will reflect ONTIME. Excluding weekends from the 3-day extension.
    Example, deadline is on 9/1/2021, output is submitted within the deadline or within 3 days after the deadline (9/1-7/2021, remark is ONTIME.

    c. If the submitted is from previous quarter or before 9/01/2021, remarks is FOR VERIFICATION

    Hoping for your answer ?

    Thank you in advance ?

      1. Thank you very much.

        This is so much helpful.

  8. Im trying to Combine two IF Functions into a single Cell:
    =IF(G11>=M13,"SUBJECT-MAJOR",IF(G11=0,"SUBJECT-NO",IF(G111, "SUBJECT-MINOR")))
    and
    =IF(I11>=4,"SUBJECT-MAJOR",IF(I11=0,"SUBJECT-NO",IF(I110,"SUBJECT-MINOR")))

    Can you help me given the scenario
    Score is 100
    20% of 100 is 20
    Scenario :
    0 and 0count is Subject No
    <20 and 1 count is Subject Minor
    <20 and 2 counts is Subject Minor
    <20 and 3 counts is Subject Minor
    = to 20 and >=1 is Subject Major
    >20 and >1 is Subject Major

    Pls Help how to combine two IF Functions in one single Cell

  9. I Want Formula

    0 to 10 = 01
    11 to 20 = 02
    21 to 30 = 03
    31 to 60 = 04
    61 to 100 and above = 05

    please suggest.....Thanks in advance

  10. Examples I have same description in A1 and different quantity in B1, the question is how i can capture all the quantity using the A1 description only. What formula should I used?

    Thanks for the answer

  11. Hello, I have created a formula that has both the IF and IF(AND) included, based on the various scenarios, but it is coming back with #Value error. Please see below. Appreciate your help. What alternative formula may I use?
    =IF(P12>=4,100%),IF(P12=3.5,P12=3,P12<3.5),50%)

    Thanks much.

    1. Hi!
      The formula is written incorrectly and will not work. But I can’t give you any advice, because I don’t know what you want to calculate. Read the article carefully. I think this will help. Or describe the task in detail.

  12. In the column 'L', I have values between 0-100. In column M, i want to fix decile class based on corresponding values in L column. Ex. If L2 value is between 0.00-1.00, i have to get 1 in M column, If L2 value is between 1.01-2, I have to get 2 in M column and so on. I tried the below formula, but error occurs,Pls help me out.

    =IF(L2>0 AND L21 AND L22 AND L2<=3,'3','0')))

  13. please help me and give me " =if " formula to paste student marks range(0 to 34, 35 to 50,51 to 70....etc) as per their marks
    ...please help me.....

    Marks 0 to 34 35 to 50 51 to 70 71 to 90 91 and Above

    A B C
    Name Marks find marks belongs to which range(0 to 34,35 to 50…etc)
    Alan 80
    Bob 50
    Carol 60
    David 95
    Eric 20
    Fred 40
    Gail 10
    Harry 80
    Ian 30
    Janice 10
    Alan 75

    David 85

      1. sorry but i didn't get , i didn't find anything from the comment section, please help me because i got stuck...only this last time give me formula ,

        column A (Name) column B ( Marks) Colmn C Range
        Sam 80 71- 80 < -- --- how to paste this text by
        using =If Formula, Cuase i just
        want to paste range(71-80)

        1. Hi!
          I gave you a link to the comment you want. Please note the paragraph above "Using multiple IF statements in Excel (nested IF functions)"

          1. i've try this formula, want to past TEXTVALUE OF ( "0-34",''35-50''OR 50-71 SON ON..) NEXT TO A COLOUMN

            A B
            35 0-35
            55 51-71
            91 90 AND ABOVE

            =if(A2=35,A2==51,A2<=70,"51-70",..so on) But i did'nt get ans in B Column text range (0-34,35-71,....so on

            JUST NEED RIGHT FORMULA TO PAST in cloumn B , " 0-35" or, "35-51", or "51-71", as per their Marks column " A"

            1. thanks i got this formula,

              '=IF(A2<=34,"0-34",IF(A2<=50,"35-50",IF(A2<=70,"51-70",IF(A2<=90," 71-90",IF(A2<=100,"91-100",IF(A2=131,"131 and above")))))))

        2. Im trying this formula ,

          =if(B2>=0,B2=36,B2=51,B2=71,B2<=80,"71-80")))

          but i did not get the actual rang(0-35...etc)

          please help me i have to solve this assignment question ....and tomorrow is my last day pls help

  14. if A2>B2, MAKE A2-B2, OR B2-A2

  15. Hi,

    I need help, if I sell every 10cartons i will get 1 carton free

    For eg:
    sell 29ctns free 2ctns
    sell 59ctns free 5ctns
    sell 10ctns free 1ctn

    Is there any formula I could use for this condition?
    Appreciate any help to solve this. Thanks!

  16. Hi Alexander,
    I have the following table:

    No. on St. Street Name
    1 A2 (European Road)
    2 A2 (European Road)
    3 A2 (European Road)
    1 E5 (National Road)
    2 E5 (National Road)
    4 A2 (European Road)
    5 A2 (European Road)

    I need a formula to number the points located on the same road (column A), in ascending order using the B column (Street name, which is text)

    I've tried to use =IF(B2B1,1,A1+1) and the result is ok if the streets are organized well (ordered by name) but for the last 2 cells, the values will be 1,2 and not 4,5.

    Can you help me with the correct one, please?
    Thanks in advance!

    2. Solved with =COUNTIF($A$2:A2,A2)

  17. Hi,

    I am trying to build an if statement which will tell me to either "Strong bet", "bet" or "fade" the capper. Conditions are ROI 20%+ for "Strong bet", ROI 10-19% for "bet". Less than 10% or sample size less than "10" from the capper is a "fade"

    thanks

  18. please help me on the formula to use here
    The college wishes to analyze the applicants’ data in order to find those applicants who qualify for admission to pursue a course in IT. Successful candidates MUST meet the following minimum requirements;
    • Must have scored a mean of 45 marks and above;
    • Must have scored 60 marks and above in Mathematics;
    • Must have scored 50 marks and above in either English or Kiswahili.

    Enter an appropriate function in cell I4 and copy it to other cells to determine whether the student qualifies for admission. If the student qualifies, the function should display ‘Successful’. Otherwise it should display ‘Unsuccessful’.

  19. Hi Alex,

    Trying to do something with AVERAGE but got stuck, hope you have some ideas!
    Let's say we count visitors in shop for last 10 days. We have 90 visitors per day for 9 days but on one particulate day we had 3x more customers then usual. I would be happy if I could exclude this day from average count ("paranormal day" or something like that). So we have days in columns A to J, row 1. In row 2, we have our daily count. K2 cell is average count (9*90+1*270). That cell with 270 value should be excluded from average count.

    Hope this above makes sense!

    Thanks
    Ivan

    1. Hello!
      To calculate the mean without anomalies, you can use the standard deviation. Only values are taken into the calculation that deviates from the average value by no more than the value of the standard deviation.
      In Excel2019 and below, enter this formula as an array formula.

      =AVERAGE(IF((A1:A10>=AVERAGE(A1:A10)-VARP(A1:A10)^0.5)*(A1:A10<=AVERAGE(A1:A10)+VARP(A1:A10)^0.5),A1:A10))

      You can also try the TRIMMEAN function:

      =TRIMMEAN(A1:A10,0.2)

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

  20. How many nested if statements can be used in Excel 2013

  21. I two columns, one has cities and the other column some cells empty,

    i will create 3rd column if the second column is empty get the data from the first column and if not empty get the data from the second column.

    Thanks

  22. My difficulty is if i chose cell from another sheet which have "DATE" formula. And i want to change that date into another date to in the cell in which i have to do the modification. So which formula i should use, So i get direct result.

    1. Hi!
      I don't understand very well what you want to do. But I hope you know that the formula only changes the value of the cell in which it is written. If this is not what you wanted, please describe the problem in more detail.

  23. Kindly help me with this -

    From(PCPM )000 To(pcpm ) Inc %
    0 0.749999 0
    0.75 1.24999 0.0175
    1.25 1.74999 0.025
    1.75 2.24999 0.03
    2.25 2.749999 0.0325
    2.75 3.249990 0.0375
    3.25 9.9999 0.04
    3.5 0.04

  24. I need help with mine.

    I have 4 categories, SA, SB, SC, SD. These categories have their consequent ranges of pass or fail.

    For example,
    If SA is less than or equal to 20, pass
    SB less than or equal to 100, pass
    SC <= 200, pass
    SD <=400, pass

    Thanks!

    1. Hi!
      Pay attention to the following paragraph of the article above — Using multiple IF statements in Excel (nested IF functions). There is an answer to your question.

  25. A=0 AND B=0,"1"
    A=0 AND B<0,"2"
    A<0 AND B<0,"3"

  26. unfortunately Using IF & AND only work for 2 cells at a time but if we have more than 2 cells/column to compare then it would not work in excel.

  27. Hi

    Would you please help me to make the formula in excel:

    1 Underweight <18.5
    2 Normal weight (18.5–24.9)
    3 Overweight (25–29.9)
    4 Obesity (BMI of 30 or greater)

    I have made this
    =IF(E2=18.5, E2=25, E2=30, "4",))))

    But it does not work.

    1. Hello!
      Have you tried the ways described in this blog post? Please re-check the article above since it covers your case.

      =IF(A1<18.5,1,IF(A1<24.9,2,IF(A1<29.9,3,4)))

  28. =IF(G10=1828,"1.770",IF(G10>2558,"1.812")))

    if > 2558 result 1.812 not working

      1. =IF(G10=1828,"1.770",IF(G10>2558,"1.812")))

        Dear Alex
        Now My G10 Value is > 2558 but the result is 1.770 (wrong result)
        By formula 1.812
        First two condition working ,

  29. Hi,

    J6= 1,62

    Why it`s not working??
    =IF(0.55>=J6<=0.8,0.55,IF(0.8<J6<=1.2,1.075,IF(1.2<J6<=1.8,1.6)))

    Many thanks

    1. Hi!
      Instead of 1.2 < J6< 1.8, you need to write a condition with the AND operator in the formula, as described in the article above: AND(J6 > 1.2,J6 < 1.8). The expression 0.55 > = J6 < = 0.8 doesn't make sense.

  30. do you have an email to send you the attachment as well?

  31. In excel calculation we have more than 4 types of rate for set of party's (ie: Wholesale Partys, Retail Partys, and other partys).

    Formulas is just one type but how could calculate for different types of partys?

  32. This article was very helpful and the IF nested within an IF formula is working with one exception. I've created the formula (below) for a table using headers
    =IF([@Cart]>=120,"5",IF([@Cart]>=100,"4",IF([@Cart]>=80,"3",IF([@Cart]>=1,"2","1"))))

    On a couple of lines [@Cart] is "0" and displays as an empty cell. Excel is returning "5" instead of "1". If I am understanding the formula correctly, my column [@CartPick] should display "1"

    e.g.;
    TM | RMAscn | Cart | DPJ | OP | FL | LTL | Problem | CartPick
    Jarod 53.17 24.13 5

    I appreciate any help you can provide!

    - Jed

    2. Sorry, it appears my comment was formated in a way that may have been hard to read after I submitted. Here is a better understanding of my table:
      e.g.;
      [@TM] | [@RMAscn] | [@Cart] | [@DPJ] | [@OP] | [@FL] | [@LTL] | [@Problem] | [@CartPick]

      [@TM] Jarod
      [@RMAscn] ""
      [@Cart] ""
      [@DPJ] 53.17
      [@OP] 24.13
      [@FL] ""
      [@Problem] ""
      [@CartPick] (this is the cell that contains the formula) 5

  33. I am Looking For Formula to to see the one date is greater than other date for multiple columns

    For Example we have multiple dates in different column for example
    Seq No. A B C D E F G

    1 26-Feb-2021 9-Mar-2021 9-Mar-2021 9-Mar-2021 9-Mar-2021 9-Mar-2021 9-Mar-2021

    Now i need to check Date for B>A C>B D>C E>D F>E G>F

    Please guide me how to use the formula to check above condition

    Thank you in Advance

    1. Hello!
      I don't really understand what kind of result you want to get. But dates can be compared in the same way as regular numbers.

      1. Hi,

        This is my formula "=IF(J7=16,AND(V7=1)*56,0)+OR(V7=2)*108+OR(V7=3)*159" & i wanted to continue it with "J7=19" then "J7=22" (parallelly changing the values of "V7") & so on..

        Plz help, if it is not possible with "IF" formula than suggest other formula to be used.

        1. Hi!
          Your formula compares numbers using the "=" operator. You are writing about how to compare dates using the > and < operators. I'm sorry, but I still don't understand what you want to do and what result you want to get.

  34. Hello
    Thank you for your helpful articles; it was really useful in teaching me how to use the functions.

    Thank you

  35. Product Code Sales currency Exchange rate Product code Sales in Euro
    E0032M 9,000.00 Euro Euro 90 E0032M
    E0032M 7,000.00 Dollor Dollor 70 E0034M
    E0032M 10,000.00 INR INR 100
    E0032M 30,000.00 Pound Pound 30
    E0032M 14,000.00 Dollor
    E0032M 20,000.00 INR
    E0034M 30,000.00 Pound
    E0034M 14,000.00 Dollor
    E0034M 20,000.00 INR

    need total sales in Euro after conversion in a single formula

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

    Need for three contain - Present, Absent & Leave, how to use the formula

    =IF(AND(A2="",B2=""),"","P",")

    1. Hi!
      I don't really understand what you want to do, but the formula can be corrected like this:

      =IF(AND(A2="",B2=""),"","P")

      1. i need extra A (Absent) or L (Leave) add that formula???

        1. Hi!
          For each of the values, you need to specify a condition. You didn't. See the examples in this article above for how the IF function works with multiple conditions.

  37. HELP! its true, if you don't use it, you'll lose it. I'm trying to say if J4-D4 is >9.5 then "Y" and if not then "N". It sounds simple but I'm so frustrated. Can anyone help me please?

    1. Hahahaha ! Sir the answer is hidden in your question itself.

  38. I'm trying to find the gender and housing status (Single or paired) in a list of animals, (this could be my data information: 7-01/001-002F or 7-001/001-002M or 7-01/001F or 7-01/001M).
    I've combined the Housing and gender in to one cell already, then I use the "IF" "OR" formulas below to find them but I would like to know if I can combine formula A with B in one cell (currently I use each formula in separate cells)
    Formula A =IF(OR(U:U="7-01/001-002F"),"1PF","1PM")
    Formula B =IF(OR(U:U="7-01/001F"),"1SF","1SM")

    1. Hi!
      Your formulas A and B cannot be combined as they contradict each other.
      The value "7-01/001F" according to formula A returns ”1PM”, and according to formula B - ”1SF”.

  39. Please advise the formula to return the value, lets say if A1*A2 is higher than 100, return value as 100 and if A1*A2 is less than 50 return value as 50 if not A1*A2.

  40. Trying to find a formula for: IF Cell A1 = X show Cell B1, but IF Cell A1 > X show B3-(B2*A1)

  41. My question is that how I can use IF with "OR" "AND" to give me the result like:
    Admin dept "yes"
    HR dept. "No"
    Securityguard " under consideration"
    Or you can suggest another logical formulas..thanks

  42. I need to find an excel formula that will take a number in a cell (example "a1") and rounds up to the nearest hundredth. It's for product increases and the number has to be even. Like $3.28, not $3.27. If I have the number $3.333 I need it to round up to $3.34. Can this be done in Excel?

    1. Hello!
      You can learn more about rounding numbers in Excel in this article on our blog.
      There you can find a formula like this:

      =ROUNDUP(A2,2)

      This should solve your task.

  43. Hi! I could have sworn I've been to this site before
    but after browsing through some of the posts I realized it's
    new to me. Nonetheless, I'm definitely happy I came across it and I'll be bookmarking it and checking back regularly!

  44. =IF((AND(I3="No",N3="Good IMU")),"Action - Feeds",""),IF((AND(I3="Yes",N3="Low IMU")),"Action - IMU")

    Can someone please help - I need 2 different return values based on 2 different and statements. help!! I can get one or the other, but I don't know how to combine into one formula!

    1. Hello!
      You can learn more about nested IF and multiple conditions in a single formula in this article on our blog.

      =IF((AND(I3="No",N3="Good IMU")),"Action - Feeds",IF((AND(I3="Yes",N3="Low IMU")),"Action - IMU",""))

  45. Hi,
    I need some help, what I`m trying to achieve is based on 4 cells. the first being a salary amount and then the next two cells (1 x figure and 1 x %) with the forth being the grand total.

    So If I wanted to give an employee a salary increase, for example; rather than give £5k in the second cell, I just enter a % in the third cell. However I would like the total salary amount to calculate the total increase whether it be a £ or % increase.

    Hope the above makes sense

  46. Hi, is it possible to generate percentage on excel with multiple arguments based on the data populated each day?

  47. I need if formula with logic if A1 column is blank data pick from A2 column
    Thanks in advance for supporting

  48. hi, is it possible to put number 1 or 2 in c1 while i used this formula in the same cell =IF((C1)=1,"I",IF((C1)=2,"II",IF((C1)=3,"III","blank")))

    i just want to put number then its automatically change or convert to text which i want it, Help me pls

      1. thanks

  49. How do I check if Column A has different values in Column B.

    E. G Apples in Column A has both fruit and veg in column B.

    Thanks

  50. I would like to create an IF statement saying in effect if a certain cell or range) is black then add it(or range) and if it is another color then deduct it.

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