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 24. Total comments: 4552

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Thank you

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

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

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

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

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

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

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

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

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

  24. =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",""))

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

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

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

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

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

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

  31. Hi there,

    I am looking to do an IF function to display a percentage. So if Cell A1 is less than 5,000 then its 8%, if its bewteween 5000 and 15000 its 10%, if its between 15000 - 40000, then its 15% and so on and so forth.

    How do I do this?

  32. HOW COULD I DO AN EQUATION THAT STATES "IF B6 IS BETWEEN 0 AND 10, THEN MULITPLY B6 BY .20,, IF B6 IS BETWEEN 10 AND 20, THEN MULTIPLY B6 BY .22,, IF B6 IS BETWEN 20 AND 30, THEN MULTIPLY B6 BY .24, ETC.....

  33. Hi,

    I am looking for an if, else function formula in excel that can identify who will "win", or "loss" in the sample situation below?
    Score
    Player #1 - 5
    Player #2 - 1
    Player #3 - 8

    In the result, Player #3 should be "win", and Player #1 and #2 should be "loss" . Is there a possible if, else formula for this? Thank you.

    1. I'm trying this formula (where H is the column in excel) ,data in H3 is 5, H4 is 1, H5 is 8
      but the result is not correct , it resulted to win, where it should be loss.

      =IF(H3>H4,"win",IF(H3>H5,"win","loss"))

      Thank you, appreciate your reply.

  34. Hello,
    can you help me to write formula that if cell B6 is:
    between 1201 to 3200, write in cell B20 number 125
    between 3201 to 10000, write in cell B20 number 200
    between 10001 to 35000, write in cell B20 number 315
    between 35001 to 150000, write in cell B20 number 500

    Appreciate your help.
    thank you
    Milan

  35. A formula for if N13=** OR * SHOW 'Y' AS RETURN VALUE

  36. I have a table sheet (CMT) with formula. it was created to monitor patient clinic attendant. I want to create other sheets that will extract data from my original table sheet (CMT). For example, I want to have a sheet that will show list of patient due for clinic appointment for the present month base on the table formatting.
    What excel techniques will I use to create such automated sheets from my table sheet?

  37. (+/-) 6 - 10% (+/-) 1 - 5% 100%
    1,000 1,500 2,000
    how to create formula that should result into this
    90% 1,000.00
    91% 1,000.00
    92% 1,000.00
    93% 1,000.00
    94% 1,000.00
    95% 1,500.00
    96% 1,500.00
    97% 1,500.00
    98% 1,500.00
    99% 1,500.00
    100% 2,000.00
    101% 1,500.00
    102% 1,500.00
    103% 1,500.00
    104% 1,500.00
    105% 1,500.00
    106% 1,000.00
    107% 1,000.00
    108% 1,000.00
    109% 1,000.00
    110% 1,000.00

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

      =IF(ABS(A1-100%)>5%,1000,IF(A1=100%,2000,1500))

  38. Hi,
    could some one help me to create a formula - if my loss is less than 2%, 100 marks, if my loss is between 2.1 to 2.5, 80 marks, if my loss is more than 2.4 - 0 marks

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

  39. hello, 1st I'd like to say I found this site very interesting; so great that you're willing to help !

    here's my question.. I have 2 lists in a column, for example, 8,6,2,-2,-4 and the second list 5,3,-2,-4,-6. now if I multiply by rows I get 40,18,-4,8,24. The issue here is that the list a ranking of points in game for 5 people. based on the 3rd column, the dude with -4 and -6 is ranked 2nd while he's actually 5th. one way to get around this is to multiply by (-1) if both values are negative. I wanted to know if there is a IF statement I can use such that (if a2<0 and b2<0 then multiply by (-1), else just multiply as usual) . I'm aware that another way to get around it is to just add the values then both negative values would remain negative; I just wanted to know if I could multiply by (-1) . that's it ! thanks for reading this. hope to hear from you this week.

  40. Thank you!!!

  41. Hello, i want to make a formula that shows me a number of 13 rows based on two criteria. One of them is
    " =IF(M3="monofazat",(R10) " and i want to put that 13 rows in " R10 " place. In the same row i want to make another criteria that sound like this " =IF(M3="trifazat",(T10) "and also shows e another 13 rows. When i write in "M3" this texts ( "monofazat" or "trifazat") i want that rows to appear in the one i write the formula. I want to make some ofertation table, to be more simple to make an offer to a client. It's possible? Thanks, i m waiting for an answer!! Have a nice day!

    1. Hi!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =IF(M3="monofazat",A2:A15,"")

      If this is not what you wanted, explain in more detail, write an example of the result.

  42. There will be a given data of a person with their Height, Weight and Mid upper arm circumferance , from that we have to derive whether the child is Severe Accute Malnutrition or Moderate Accute Malnutrition or Normal or Obese. What will be the formula in Excel . Length (cm) "SAM
    < –3""MAM ≥ –3 to +2 to ≤ +3" "Obesity> +3"
    Weight (kg)
    45 0–1.8 0–1.9 1.9 2.0–3.0 > 3.3
    46 0-1.9 2.0–2.1 2.2–3.1 3.2–3.5 > 3.5
    47 0–2.0 2.1–2.2 2.3–3.3 3.4–3.7 > 3.7
    48 0–2.2 2.3–2.4 2.5–3.6 3.7–3.9 > 3.9
    49 0–2.3 2.4–2.5 2.6–3.8 3.9–4.2 > 4.2

    1. Hi!
      The result you want to get me is incomprehensible. But recommendations from this article will be useful for your formula.

  43. I need a formula that can look at value in B column (there will be up to 10 different values in this column)
    and return a different result in the next cell of column C IE: cell b1 = text then c1 = text2

  44. Hi There!
    Looking for some help.

    I have a scoring card and need to calculate points on a ranking scale. What would be the correct formula for the below.

    Score is 20 or less receive top points, if score is higher than 20 subtract point. (-2)

    =IF(D920,$F$34,(D9*F$34)/20-2))
    OR
    =IF((D820,$F$34,(D8*F$34)/20),-2))

    Thanks

    1. Hi!
      Unfortunately, without seeing your data it is impossible to give you an exact formula. Your conditions are not entirely clear to me. But you can do this if you carefully study the instructions in this article above.

      1. Hello,

        We need to check value of two column and IF BOTH ARE POSITIVE "LB",if 1st is negative and 2nd is positive "SB",if both are negatie "LU" else "SC")
        1st 2nd
        -1.62% 169.90% LB
        2.01% 44.29% SB
        -0.78% 15.46% SB
        -0.74% 7.80% SB
        -0.10% 4.16% LB
        1.95% 2.91% SB
        -0.19% -1.32% SB
        0.89% 1.92% LB
        3.11% 1.33% SC
        -1.47% -7.96% SC
        2.25% 3.83% SC
        -0.85% -2.97% SB
        0.41% -3.17% SC
        0.05% 0.42% SC
        -0.07% -3.88% LU
        -0.61% -3.52% SB
        0.49% -2.44% SC
        -0.95% -0.84% LU
        -2.37% -5.69% SC

        Please help me with correct formula.

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

  45. I want to put the "statements in column 2" when I select "items in column 1" from menu bar in another sheet.

    Column 1 Column 2
    item1 sample for statement1
    item2 sample for statement2

    I tried pivot table, but it provides statement upto some extent, I need to display the whole statement in column 2, no matter how bigger it is. Please help!

  46. I had read all the examples, and maybe I miss it but I can't figure it out a formula:

    If the value of cell A1 is equal or more than 10% the amount will be $500.00 but if the same cell is less than $500.00 it will be 10% less. Can you please help me? I do appreciated

  47. Hi,
    I have a spreadsheet with a Table tab and a Data tab. In the Table I have a persons name and then columns for each different inspections that are completed - I'm trying to work out whether a VLookup or a some sort of IF or SUMIFS variation can be used to search the information in the 'Data' tab. Colum C in Data is the names and D is the type of inspection. The Data is C3 : C20 (names) and D3 : D20 Inspection type (example 'Assurance - Task Inspection') I need the table to have = QTY of John Smith's Task Inspections. Ideally I would like to include in the formula 'name' and 'inspection type' as the report evolves and we include more people or inspections all that would need to be changed is the formula 'name' reference or 'inspection type' reference.
    Note: The data tab will be copied from a system report and pasted into those rows everytime

      1. Thanks Andrew,
        I managed to work out a SUM and COUNTIF combination and trial and error the formulas until it worked out.

  48. Thank you very much guys, this article was very helpful in getting me to complete an urgent caluclation.

    well explained and simple.

    Many thanks

  49. Hello ,

    Could you please help me on the below formula? I'm trying to define the below rule:
    The formula that I have defined is this =IF(K2>=I2,"Achieved",IF(K2<I2,"Not Achieved"))
    but I need to add another condition so if the method of meeting colum2 is Phone Call whatever value it might have not to be calculated as Achieved.

    2 Meeting 1 Not Achieved
    1 Meeting 0 Not Achieved
    6 Meeting 6 Achieved
    6 Meeting 6 Achieved
    6 Meeting 2 Not Achieved
    6 Meeting Not Achieved
    1 Meeting Not Achieved
    0 Phone Call Achieved

    1. Hello!
      If I understood correctly, add the condition to the formula

      =IF(J2="Phone Call","Achieved",IF(K2>=I2,"Achieved",IF(K2<I2,"Not Achieved")))

  50. Hi, i have been trying very hard to display the length of service for some vouchers. Appreciate your help!

    Category > = 2 Years < 2 Years
    Manager 70.00 50.00
    MT & ASM 50.00 40.00
    FT SA 40.00 30.00
    PT SA 30.00 20.00

    and it depends on the category and less than 2 years or more than 2 years.

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