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 18. Total comments: 2538

  1. Hello all,

    I am trying to figure out a function to group states into regions. I found out how to group individual states into regions, however I need a formula that will group multiple states together. For example, Texas is in region 6, however I need to group all of the states that are in region 6 together. I am using the current formula:

    =IF(Cell of state="StateAbbreviation","Region", IF(Cell of state="stateabb2","region 2"))

    Any help would be wonderful! Thank you!

    1. Do NOT use IF for this.

      1) Create a table with the state (or abbreviation) in the first column and the region in the 2nd column. For this example, let's say this information is in this range. f2:g51

      Ca West
      Ia Midwest
      TX South
      Fl Atlantic
      Co Mountain
      ...etc

      wherever you need the region use this formula:
      =VLOOKUP(A6, f2:g51,2)

      A6 in the above example is the state

  2. Hello, I'm trying to evaluate a series of values, to determine if the value in 2 or more cells is greater than 0. Any help you could provide would be awesome.

    Thank you!

    1. =if(COUNTIF(D4:D20,">0")>=2,"Yes...2 or more cells is greater than 0","Nope")

      D4:D20 represents your "series of values"

  3. I have data in which in three columns there is ..

    yes yes no, two yes means eligible other then not eligible..

    yes no yes = Eligible
    yes no no = Not Eligible
    no yes yes = Eligible

    Please suggest the formula .....

    YES YES NO Eligible
    YES NO YES Eligible
    NO YES YES Eligible
    YES YES YES Eligible
    NO NO NO Not Eligible
    NO YES NO Not Eligible

    1. =IF(COUNTIF(a10:c10,"Yes")>1,"Eligible","Not Eligible")

      Note: countif is NOT case sensitive

  4. Hey, I'm trying to write an IF formula to show how many people are required to perform a task. For example say people work 7 hours a day.
    If 0-7 hours = 1 person
    If 7-14 hours = 2 people
    If 14-21 hours = 3 people
    If 21-24 hours = 4 people
    The formula I wrote appears to stop after the first logic argument and not apply the next part of the 'IF' correctly. Any help much appreciated.

    1. How about a NO LOGIC formula. I think this is a much easier formula and it scales to as many hours you need without changing the simple formula.

      B1 contains the number of hours a project requires.
      hoursPerWorkerPerDay contains 7 (in your example)

      =INT(0.999+(B1/hoursPerWorkerPerDay))

      Or more simply

      =INT(0.999+(B1/7))

      Why add 0.999 to each value before dividing by 7? If a job will take 6 hours, 6/7 = 0.857... Effectively adding 1 to this, returns 1.857... INT returns the integer value or 1. This meets your spec.

      Why not just add 1? Try it! Say a job takes 7 hours. =INT(1+7/7) returns 2 whereas 0.999+7/7 returns 1.999. Taking INT(1.999) returns 1. This meets your spec.

      Nested logic in If statements get very complicated, very quickly. They are hard to read and troubleshoot. Of course sometimes this may be necessary but I always look for another way.

    2. Hello, Jonathan,

      it's hard to tell why your formula doesn't work properly since you haven't included it :) But I can offer you to try the following one (assuming that the number of hours is in A1):
      =IF(AND(A1>0,A1<=7),"1 person",IF(AND(A1>=8,A1<=14),"2 people",IF(AND(A1>=15,A1<=21),"3 people",IF(AND(A1>=22,A1<=24),"4 people",0))))

      Note that it will return 0 if the number of working hours is also equals to 0.

      Hope this is what you need!

      1. Thanks so much for getting back.
        My original was below (assuming U3 was the cell value in the below.
        =IF(U3<7,"1 person",IF(U3<14,"2 people",IF(U3<21,"3 people",IF(U3<24,"4 people","5 people"))))
        I tried using your formula but still no joy.
        For example if cell U3 is say 13:30 (13 hours 30 mins) of work and a worker worked 7 hour shifts then you would need 2 people to cover 13 hours and 30 mins of work but this doesn't seam to work and stops after the first logic test.
        I have my reference cells (e.g. U3) formatted as HH:MM:SS does this affect it?
        Thanks again

        1. =IF(AND(C3>=0,C37,C314,C321,C3<=28),4,IF(C3="",0,5)))))))

          cell reference is C3

          1. =IF(AND(C3>=0,C37,C314,C321,C3<=28),4,IF(C3="",0,5)))))))

  5. I need a formula for below conditions:
    =if(749>A1>500 then B1*5%, (999>A1>749 then B1*7.5%, (A1>1000 then B1*10%)))

    please revert me if it is possible.
    Thanks

    1. Try this

      =IF(AND(749>A1,A1>500),B1*5/100,IF(AND(999>A1,A1>749),B1*7.5/100,B1*10/100))

    2. I'VE NEVER GOT A REPLY FOR THIS TYPE OF SITUATION...
      SO IT'S OKKKKKKK..

  6. HI!

    how can I make function SUMS IF instead SUMIFS? :D IF A1=a Than SUM one range, IF A1=b than SUM different range?

    thanx

    1. Hi, Dino,

      I believe this is what you need for this particular task:
      =IF(A1="a",SUM(B:B),IF(A1="b",SUM(C:C),""))

      You can add more conditions to sum other ranges by nesting IFs instead of double quotes at the end of the formula.

  7. Hi!

    Can someone help me with the below?

    I am trying to create a formula in excel to populate the previous columns answers into a points system shown below. The data is being pulled form a research database so the previous column will state the answers show below verbatim. I tried the formula but I keep getting an error. Any advise??

    Too High - "I'm a lot worse than I thought" (1 pt)
    Too High - "I'm somewhat worse than I thought" (2 pts)
    Just Right - "My expectations were met" (3 pts)
    Too Low- "I'm somewhat better than I thought" (4 pts)
    Too Low- "I'm a lot better than I thought" (5 pts)

    =IF(AM4="Too High - "I'm a lot worse than I thought" (1 pt)", "1", IF(AM4="Too High - "I'm somewhat worse than I thought" (2 pts)", "2", IF(AM4="Just Right - "My expectations were met" (3 pts)", "3", IF(AM4="Too Low- "I'm somewhat better than I thought" (4 pts)", "4", IF(AM4="Too Low- "I'm a lot better than I thought" (5 pts)", "5","0")))))

    1. Hi, Chelsea,

      you're most likely getting an error because of the double quotes between the phrases: Too High – “I’m a lot worse than I thought”
      Try replacing them with single quotes or delete them completely, so that Excel could treat your text values in a proper way.

  8. Hi there,
    I'm hoping you might be able to help if it's not too much trouble.
    Based on the format of the formula in this thread, I've built this formula:
    =IF((AND(ISBLANK C4, B4=B3)),C3,"")

    but this returns a result of #NAME?.

    Can you tell me what is wrong?

    Basically, I want the formula to first establish that C4 is empty AND that the value in B4 is the same as the value inB3 and then if that is all true, put the value from C3 into the formula field.

    I am wanting to populate gaps in 2 columns of numbers that have correlating values that need filling in to the second column in some places but not all.

    Example data:
    Row2 (A)Image IRN (B)Object Reg (C)Object IRN
    Row3 12345 555 ObjectIRN01
    Row4 412458 555
    Row5 36598 555
    Row6 12478 222 ObjectIRN02
    Row7 13697 222

    So, for example, I would want the value "ObjectIRN01" to be displayed if C4 is blank.

    Are you able to assist?
    Any help greatly appreciated!
    Karen

    1. Just in case this helps anyone else, I have since built a list of the reference values and then used an INDEX formula to populate the blank column as required... works like a dream!
      Karen

    2. Hi again, note that I put together an IF AND statement that runs without error.......

      =IF((AND(ISBLANK(BLANK C4),B4=B3)),C3,"no match")

      ...however, in the case where B4 does equal B3, where it should place the value from C3 into the cell where the formula is, it says "no match".

  9. how do i create this formula? I want B17 to show as follows

    =if B17 is BASIC should show $0.00 but if PREMIERE should show $85.00 how to create this?

    1. Hi!

      Here's the formula:
      =IF(B17="BASIC", 0, IF(B17="PREMIERE", 85, ""))

      Just enter the formula in a cell, and then apply the currency format to that cell (the fastest way is to press the Ctrl+Shift+$ shortcut).

  10. Hi, I am trying to create a formula for the Following situation:
    For Cell E9:
    -If the # in E7 is equal to a number in A 2-20, print the corresponding percent to that number from B 2-20 in cell E9.

    Example:

    A B ..................E
    1 12%
    2 15%
    3 19% (E7): 2
    4 23% (E9): 15%

    I know this isnt the most clear way of writing this question, but its the best I have with my limited excel knowledge at this time. Any Help is much Appreciated. Thanks!

    1. Hi, Austin,

      try the folowing in E9:
      =VLOOKUP(E7,A1:B4,2)

      (Make sure, to make E9 to indicate percentage. It can be set up under Home tab, Number group).
      You can also learn how VLOOKUP works here.

  11. I need a formula that if D4>=10 and F4=yes then "MDR"

    I am not sure how to write this.

    Thanks,
    Sandra

  12. How can I get the brand name of this item? Who can help me with my problem? TIA

    B JORDAN M SIZE 43 BUR 52 - E
    ADIDAS M SIZE 42 DE101 5 52
    LACOSTE F SIZE 36 842 C90 52
    G VANS M SIZE 38 857 C10 55

    1. Please, specify your task: what brand name (you have a list of 4), what do you want to do with it (them) exactly – to return the value into another cell or something else? Give us more details about your task so we could help you.

      1. Hello Natalia,

        This is the example of my work, how can I get the brand of my item in column b using formula? Thanks

        Example:

        Column A Column B
        B JORDAN M SIZE 43 BUR 52 - E JORDAN
        ADIDAS M SIZE 42 DE101 5 52 ADIDAS
        LACOSTE F SIZE 36 842 C90 52 LACOSTE
        G VANS M SIZE 38 857 C10 55 VANS

        1. Column A ||| Column B
          B JORDAN M SIZE 43 BUR 52 - E ||| JORDAN
          ADIDAS M SIZE 42 DE101 5 52 ||| ADIDAS
          LACOSTE F SIZE 36 842 C90 52 ||| LACOSTE
          G VANS M SIZE 38 857 C10 55 ||| VANS

  13. I need a formula for follwoing

    if cell A1:A5 = "A" divide some number by 5
    if cell A1:A4 = "A" and A5 does not have any entry then divide some number by 4 .

    1. If your number to divide is in column B, and you want to check whether there's 'A' in every A-cell, then try this formula:

      =IF(AND(A1="A", A2="A", A3="A", A4="A"),IF(A5="A",B1/5,IF(A5=" ",B1/4,"")),"")

  14. Hello.

    can someone, please, help me?

    I have a column A and a column B.
    In column B I can have up to 99 values (text values/words/phrases) and I want in column A to result another specific value, depending of what value is written in column B.

    For example, in column B I have: red, green, white, yellow, black etc.

    And in column A i would like to obtain: Aple if column B is red, green or white or.... so on, Mango if column B is green, black, yellow or....so on, and Banana if column B is purple, blue, orange or... so on.

    Is there any formula that can help me?

    Thank you!

    1. Hello, Sasha,
      in your case IF function would be too long and complex.
      What may help you better is a VLOOKUP function. It helps to find and return the data inside a large table. But for that you may need to create an additional small table (you can do this on the same sheet) with corresponding matches, like:
      red - apple
      orange - mango
      yellow - banana
      white - coconut
      and so on

      Then try to apply VLOOKUP function.

  15. How can I make excel leave cells blank if a cell in a calculation is blank. I used to use =if(A1,"","")(a1+b2)

    1. Hello John,

      Try this one: =IF(A1="","", A1+B2)

  16. I need to calculate some incentives as below tier:
    >=10 pcs sold, incentive $0.20 per pc
    >=20 pcs sold, incentive $0.50 per pc
    >=30 pcs sold, incentive $0.80 per pc

    eg. if the sales staff sold 28 pcs of the item, he will get $0.50 x 28 pcs = $14

    How to set formula (using 'IF')?

    1. Hello, Kim
      assuming that the number of sold pieces is in A1, the formula will look like this:

      =IF(AND(A1>=10,A1<20),A1*0.2,IF(AND(A1>=20,A1<30),A1*0.5,IF(A1>=30,A1*0.8,"")))

      Note, that it doesn’t include the numbers that are less than 10, but the result returned for them can be set instead of two quotes at the end of the formula.
      To learn more about the function, feel free to read the info on how to create nested IF function together with AND function inside it.

  17. Hi guys,
    My question is:
    Is is possible to make an schedule by making table, for exmaple:
    I have workgroup of 15 people and only 11 must be on daily work, so 4 people must rest per day.
    I wanna make a mix formula and my idea is to be like that:
    "W" = work
    "R" = Rest.
    So for "w" should be "0" and for "R" must be "1" , then i can easily calculate how many of them will rest without counting it manually,because the formula will calculate it.
    It must be with =SUM(IF(U6:U20="W",0,1))
    http://imgur.com/a/MRXI4 a quick example just with "0" and "1" i just do not know how to make the formula, if on the cell is "W" must be understand as "0" and vice verse

    1. Hey, Alex,
      there's another simpler function you can use, whether you have numbers in cells
      =COUNTIF(U6:U20,0)
      or letters
      =COUNTIF(U6:U20,"W")
      It returns the number of those "Ws" you are trying to sum up.
      Read here to find out more about the function.

  18. My question is around the "then" part of the logical test. I want to say "if someone has selected "no" from the picklist, then put "X", if someone has selected "yes" then insert picklist" (with the second picklist being a new one.

    E.g. Is it under contract? (dropdown picklist "yes/no". If "yes" is selected, then you need to make a selection in the next column from a dropdown list "Company/Landlord/Dual"

    Is this at all possible?

    1. Hello, Tania,
      yes, it is somewhat possible. For that you need to create a dependent drop-down list, but your "X" will have to be a part of another drop-down list (it can even be a single choosing option). Using your data, try to follow the steps described in the article.
      However, if you want "X" to appear as a simple text without any drop-down lists, you need a special custom script; and, unfortunately, we won't be able to help with it.

  19. Can i ask what formula can i use to count for example: i want to count specific name in a column range a2:a5 only if column range b2:b5 has numbers. For example b2=12 and a2=mark, then it is counted. Another if b3 is blank and a3=mark then mark is not counted. So in range a2:a5 has 1 mark name only... sorry if it is confusing.

  20. The three above make one line I put in the cell. There should be a 0 after the I2>

    Whew!!

    1. Hi Maria,

      First off, our apologies for mangled formulas. Our blog engine has problems with posting formulas containing < and > signs.

      In case of nested IF's, the conditions are checked in the order they appear in the formula, and if the first condition is TRUE, others are not checked. So, you simply put the I2>0 statement first, like this:

      =IF(I2>0, "Completed", IF(E2<0, "Expired", IF(E2<10, "Expiring soon", "")))

  21. Having a problem with if then. For a promotion roster if A1=1 then private, if A1=2 then private first class, if A1=3 then lance corporal.......

    Here is current formula

    =IF(J2=1,"Private",IF(J2=2,Private First Class,IF(J2=3,Lance Corporal,IF(J2=4, Corporal))))

    It will work for the first function (J2=1) but no other.

    Thanks
    Will

    1. Correction...here is current forumula.

      =IF(J2=1,"Private",IF(J2=2,”Private First Class”,IF(J2=3,”Lance Corporal”,IF(J2=4,“Corporal”))))

      When I type 1, Private is properly displayed. All other numbers (2, 3, 4) result in a #NAME? error

      1. Solved it. Had to do with improper quotation marks. Not sure how, but they seemed to be facing the wrong way.

  22. Hi I need help with my formula.
    K = start date
    H = due date
    J = date completed
    K = overdue days '=IF(I6="",-100,(IF(NOW()0.15,"Overdue","Open"),IF(K7>0.15,"Closed Late","Closed"))

    I want to add to task status that if start day is more than today it must be "in progress" if less than today then "Not Started. but keep the rest that is in J. How do i do it... I have tried but get a value error or the rest does not work

    Please help me.

    Anne

    1. Apologies lets try this again.... need to keep L status and add "in progress" and "Not Started"

      K = start date
      H = due date
      J = date completed
      K = overdue days
      L = Task Status

      the formal for K is =IF(I7="",-100,(IF(NOW()0.15,"Overdue","Open"),IF(K7>0.15,"Closed Late","Closed"))

      I would life to add to L the status "In progress" and "Not Started" thus if H is less or more than today but keep the rest of L

      Thanks :-)

      Annemarie

  23. Very helpful. Brilliant work. God bless you.

    1. Why was this comment?

  24. Hi, I am trying to do up a pay sheet. I'm sure the answer is simple, but I can't think of it, so would appreciate your help. In row 1 I have Mon through to Sun. In row 2 I have the hours worked each day. The formula needs to show ordinary hours and overtime hours. So, for example, I imagined that using and if function: =if(a2<=8,A2,8,+if(b2<=8,B2,8,+if(B3<=8.... and so on for the week. Obviously the + isn't adding the cells to give me a final result. Can you help please.

    1. Sorry, got it sorted

  25. I want to check mutual condition in between two columns ex (a1=1,b1=2)=(a2=2,b2=1) shows error but not using duplicate condition

    1. Hello Pratap,

      To check multiple conditions, embed the AND function within your IF formula. For example:

      =IF(AND(a1=1,b1=2), value_if_true, value_if_false)

  26. I have two conditions before performing nested IFs. The nested IFs, if true will perform a calculation and if False will yield Zero as a result.

    is that even possible?

    1. Hi Sherwin,

      I believe it's possible and you can express those conditions using the AND function. To be able to suggest a formula I need to know the details of your task.

  27. Hi,

    Please help, I need to make this formula shorter because my data are kinda plenty.
    Thanks in advance :)

    =IF(ISNUMBER(B1),1*A$1,"0")+IF(ISNUMBER(B2),1*A$2,"0")+IF(ISNUMBER(B3),1*A$3,"0") ...

    1. Hello Kim,

      Just write the formula for the topmost cell (row 3 in your case), and then drag down the column to copy to other cells:
      =IF(ISNUMBER(D3), 1*C3, 0)

      Be sure to use relative cell references (without the $ sign), to instruct Excel to adjust the formula for each cell where it is copied.

  28. Hmmm, when i post my formula, the website does not populate it how i have it written.

    1. Hi Seth,

      Sometimes out blog mangles formulas in comments, sorry for this. In general, you can use the following syntax:

      =IF(B19<100, formula 1, formula 2)

      The formula tells Excel to calculate formula 1 if B19 is less than 100, formula 2 otherwise.

  29. I'm trying to write a formula that will subtract A1 from B1 and if the difference is less than 10, multiply F12 by .25 however if greater than 10 but less than 18 multiply by .50 and if greater than 18 multiply by 1.
    Any help would be appreciated.

    1. Hi Kathy,

      Try the following formula:

      =IF(B1-A1>18, F12*1, IF(B1-A1>10, F12*0.5, F12*0.25))

  30. Hello Svetlana,I'm lee. Nice to meet you. I have some difficulties in applying multiple condition based on one column of data set.

    I had created one formula based on one data set as "condition 1" and it worked.
    "=COUNTIFS(Sheet1!$D3:$D10000,"2012",Sheet1!$O3:$O10000,"No",Sheet1!$AD3:$AD10000,"No",Sheet1!$AY3:$AY10000,"May.2016")"

    However, when i created another different formula base on the same data set as "condition 2", it cannot work.

    =COUNTIFS(Sheet1!$D3:$D10000,"2012",Sheet1!$O3:$O10000,"Yes",Sheet1!$AD3:$AD10000,"No",Sheet1!$AY3:$AY10000,"Nov.2016")

    Can you please help me to solve this situation? I had tried to do it but I can't solve it. Thank a lot.

    1. Hi Svetlana. I can send you one sample of that problem. Please help me to solve it. Thank a lot. =D

  31. Hi Svetlana,

    I'm looking to populate a cell value with a number based on how many cells have the colour green.

    So:
    A1 B1 C1 D1 (E1- Answer '1' will appear here)
    G NG NG NG (G= Green, NG= Not Green)

    Another example:
    A1 B1 C1 D1 (E1- Answer '2' will appear here)
    G G NG NG (G= Green, NG= Not Green)

    If all cells are not green, then 0 will appear here.

    What I have so far:
    =IF(GetFillColor(A1)=4,"1", IF(GetFillColor(A1:B1)=4,"2"), IF(GetFillColor(A1:C1)=4,"3"), IF(GetFillColor(A1:C1)=4,"4", "0"))

    Any help would be much appreciated, Thanks.

    1. Sorry, a slight change. Because a CountIF I don't think will work.

      Answer change: I would like to use percentage instead?

      example 1:
      A1 B1 C1 D1 (E1- Answer '15%' will appear here)
      G NG NG NG (G= Green, NG= Not Green)

      Another example:
      A1 B1 C1 D1 (E1- Answer '30%' will appear here)
      G G NG NG (G= Green, NG= Not Green)

      1. Need a formula for the following please;
        If Cell D2 = "QLD" and Cell F2 = "5", then (5*200),
        If Cell D2 = "QLD" and Cell F2 = "6", then (6*200),
        If Cell D2 = "QLD" and Cell F2 = "7", then (7*200) etc.

  32. Hi i need some help,

    D11>=TODAY()-7 is not working in below formula, i would like to return result, if DateToday >= 7 days

    =IF(AND(A11="TEST",B11="MT",D11>=TODAY()-7,(H11=IF(H11="","","N")),(L11=IF(L11="","","N"))),"ALERT 1.1",IF(AND(A11="TEST1",B11="MT",(H11=IF(H11="","","N")),(L11=IF(L11="","","N"))),"ALERT 1.1","OK"))

    This is the result expected. Anyone can help convert this into above formula?
    =(IF((D11>=TODAY()-7),"Y","N"))

    Thank you so much

    1. Please help to add =(IF((D11>=TODAY()-7),"Y","N")) into

      =IF(AND(A11="TEST",B11="MT",

      D11>=TODAY()-7, (THIS ONE!I tried multiple times but not working)

      (H11=IF(H11="","","N")),(L11=IF(L11="","","N"))),"ALERT 1.1",IF(AND(A11="TEST1",B11="MT",(H11=IF(H11="","","N")),(L11=IF(L11="","","N"))),"ALERT 1.1","OK"))

  33. Hi i need below function

    I need result in C with below condition

    A column=300/200/100 or below 100 and B column 60/40/20 or below 20

    if A1>=300 & B1>=60 then gold
    if A1>=200 & B1>=40 then silver
    if A1>=300 & B1>=60 then Bronze otherwise it should be "Not Applicable"

    1. Hi i need below function

      I need result in C with below condition

      A column=300/200/100 or below 100 and B column 60/40/20 or below 20

      if A1>=300 & B1>=60 then gold
      if A1>=200 & B1>=40 then silver
      if A1>=100 & B1>=20 then Bronze otherwise it should be "Not Applicable"

  34. Hi Svetlana,

    Great tutorial. I have a spreadsheet with cells that are differently coloured for different markets. I.e. Green - UK market, Yellow - EMEA market. I just need to do a sum of all those in green (i.e. UK), istead of manually adding the cells, is there are local test i can perform to add it automatically.

    I.e. IF (E1=GREEN,'UK','OTHERREGION') OR

    SUMIF CELLS ARE HIGHLIGHTED GREEN?

    Thanks,
    Andreea

    1. Hi Andreea,

      Microsoft Excel does not have a function to sum cells by color, so we took a step forward and created our own one. The VBA code and detailed instructions are here: How to count and sum cells by color in Excel.

      Please pay attention that a different code should be used for cells colored manually and those colored with conditional formatting.

      Alternatively, you can use our Count & Sum by Color for Excel add-in.

  35. Hello.
    Apologies if you have already answered this but i want to return a number result where there is specific text in two cells.
    i.e a1 = Green b1 = Yes
    a2 = Green b2 = No

    How many are green = 1.
    I know that this is probably an nested IF/AND formula but i just can't seem to get it to work.
    Many thanks.

    1. Hello Rebecca,

      If my understanding is correct, you want to count how many cells have "green" in column A and "yes" in column B. If so, you can use the COUNTIFS formula like this:

      =COUNTIFS(A1:A10, "green", B1:B10, "yes")

  36. Hi
    I have one value in a cell, say C3, i want result in C4 testing 3 conditions i.e C3 must be equal to or greater than 4 but less than 8 to get value of 0.5 otherwise result value 1 and if value in C3 is less than 4 than result value should be 0. Please can anyone help!!!

    1. Hi!

      Try this formula:
      =IF(AND(C3>=4,C3<=8), 0.5, IF(C3<4,0,1))

  37. Good afternoon,
    I was trying to write the following function:
    =IF(H4" C4/2, C4/(2^H4))
    The problem is adding the ">" symbol before the number given by C4/2

    Thanks in advance for your time

    1. Hi Abed,

      Sorry, I don't quite understand the challenge. Just type it without double quotes:
      =IF(H4>C4/2, C4/(2^H4))

  38. Hi have a great day,
    i want the formula the below items with condition as follows

    1.Pass & A Excellent
    2.Pass & B Very good
    3.Pass & C Good
    4.Pass & D Improve
    the above all conditions needs in single formula
    i try to find only one condition =IF(AND(A2="Pass",B2="A"),"Excellent","") but i don't know how to continue the rest of the conditions in the same cell.

    Result Grade Remarks
    Pass A Excellent
    Pass B
    Pass C
    Pass D
    Pass A
    Pass A
    Pass B
    Pass A
    Fail D
    Fail B

    1. =IF(AND(A13="Pass",B13="a"),"excellent",IF(AND(A13="pass",B13="b"),"very good",IF(AND(A13="Pass",B13="c"),"good",IF(AND(A13="pass",B13="d"),"improve",""))))

      1. Thanks a lot..That was helpful for me too!!

  39. Hi please help me. I've been cracking my head for almost a week now.I need a formula to sum up this

    If column is 1 then multiply with 0.65
    If column is 2 then multiply with 0.55
    If column is 3 then multiply with 0.45
    If column is 4 then multiply with 0.35

    1. =IF(A1=1,A1*0.65,IF(A1=2,A1*0.55,IF(A1=3,A1*0.45,A1*0.35)))

  40. Hi,
    I need your advice. I can't find a way to write a formula for multiple logic from 2 different cells.

    To determine "Moving" from one of the cell, let's say A1 is "Yes".
    To determine "Sales" from one of cell, B1 is any number more than "0".

    Here are the logic:
    1. With Moving & With Sales = Moving
    2. Without Moving & without Sales = Non-moving
    3. With Moving & Without Sales = Others
    4. Without Moving & With Sales = Others

    So, what should I put C1 for the formula.
    I tried combination of IF (AND & OR) but failed.

    =IF(AND(E2="Yes";G2>=1);"Moving";"")&IF(AND(E2="No";G2=0);"Non-moving";"")&IF(AND(E2="Yes";G2=0);"Others";"")&IF(AND(E2="No";G2=0);"Others";"")

    It ended up with 2 results in 1 cell. For example, Non-moving & Others.

    Please help.

    Thank you so much!

    1. I managed to solve it.

      =IF(AND(E17="Yes";G17>=1);"Moving";"")&IF(AND(E17="No";G17=0);"Non-moving";"")&IF(AND(E17="Yes";G17=0);"Others";"")&IF(AND(E17="No";G17>=1);"Others";"")

  41. Hi I want to creates system that assigns roll numbers to my students.
    the conditions are:
    if 1-99 then it should display roll as SA followed by the number eg roll assignment should be SA1 for the 1st student, SA2 for 2nd student etc.
    For the next 100-999 then it should display roll as SB1 for the 100th student,SB2 for the 101st student.

    1. Hello Lavanya,

      If you have serial numbers from 1 to 999 in some column, say column A, you can use the following formula:

      =IF(A1>99, "SB"&A1-99, IF(A1>0, "SA"&A1, ""))

      Or, you can generate roll numbers based on the row numbers in a worksheet:

      =IF(ROW()>99, "SB"&ROW()-99, "SA"&ROW())

  42. Hello,

    Please help, I need formula for below condition:

    If 0-30 should show "C", if 31-60 should show "B", if 61 above should show "C".

    Thanks

    1. Hello!

      Here you go:

      =IF(AND(A1>=0,A1<31), "C", IF(A1<61,"B", "A"))

      Please note that your conditions have "C" for 0-30, and >61, which is obviously a misprint, so I used "A" for the latter.

  43. =IF(B3>69,20(OR(B3>50403010<29,2))))))))). Not sure my formula showed up completely.

    1. Hello Barbara,

      It looks like the formula got posted all wrong, sorry for this. Can you please describe the conditions in words?

      For example:
      - If B3 is greater than 69, return 20
      - If B3 is greater than 50, then what? etc.

      1. If b3 is greater than 69, return 20
        If b3 is greater than 50, less than 68, return 15
        If be is greater than 40, less than 49, return 10
        If b is greater than 30, less than 48, return 5
        If b3 is less than 29, return 2

        I hope this one is more clear. Thank you Svetlana

        1. Hello Barbara,

          You can us the following nested If's:

          =IF(B3>69,20, IF(B3>=50,15, IF(B3>=40,10, IF(B3>=30,5,2))))

  44. Hi,

    I'm new to this but want to do the following:

    if cell D11 is 6 then then the payment is £450 plus £60 for each additional number

    ie. 9 people attended = £450 + (3*60) = £630

    Can anyone help?

    Thanks in advance

    1. Sorry but some of my original message went AWOL.

      if D11 is 6 = £450 + £60 for each additional number (ie 9 people attended = £450 + (3*60) = £630

      Thanks

      1. Hi Alison,

        If my understanding of the task is correct, the following formula should work a treat:

        =IF(D11=6, 450, 450+(60*(D11-6)))

  45. Hi Svetlana,

    I want to calculate the difference between date 1 "I3" and date 2 "L3", but in case of date 2 "L3" not available I want to calculate it between 1 "I3" and today date so, I wrote this formula but I keep receiving wrong result cause of not applying today formula
    =IF(L3,"=(TODAY())-(I3)",(L3-I3))

    1. Hi Muahammad,

      Try this formula:

      =IF(L3="", TODAY()-I3, L3-I3)

  46. Hi!
    This is my current formula: =if(and(ISBLANK(F2),ISTEXT(G2)),"New",)
    Right now, if there is no text in F2 AND there is text in G2, the cell will say "New"

    I want to add to the formula so it's this way:
    If there is no text in F2 AND there is text in G2, the cell will say "New" but IF there is text in G2 and F2, the cell should say "Ad Proofing"

    Could someone help me with this? Thank you!

    1. =if(and(ISBLANK(F2),ISTEXT(G2)),"New","Add Proofing") try this should help

  47. Dear Sir,
    How can use over 9 conditions.

    =IF(H11=2,I19+H19+H20+H21,IF(H11=3,I20+H20+H21,IF(H11=4,I21+H21+H22,IF(H11=5,I22+H22+H23,IF(H11=6,I23+H23+H24,IF(H11=7,I24+H24+H25,IF(H11=8,I25+H25+H26,IF(H11=9,I26+H26+H27))))))))

    1. It may have to do with the version of Excel you are using. If it is an older version, I believe you can only have a maximum of 7 conditions. The newer versions allow up to 64. You may want to look into that.

  48. I am trying to make an If then statement for the following:

    If box F5 = 100, then box F6 = 3000

    if box F5 100, then box F6 = 5 x every number over 100 + 3000
    (example if F5 = 105, the box F6 would need to be (5*5)+3000

    Could you help me make a formula? Please?

    1. it cut off part of my formula:

      also add in: if box F5 < 100, than F6 = 3000

      1. Hi Emily,

        Try the following formula:

        =IF(F5<=100, 3000, 5*(F5-100)+3000)

  49. I have a spreadsheet that has a due date in column j, and a status of open or closed in column K.
    I need Column L to calculate if the date is after today's date, and the status is open, then it says On Track, if the date is after today's date and the status is closed, then it says completed, and if the date is before today's date and the status is either open or closed then it shows Delayed... I've figured out how to get it to show On Track, Completed and Delayed based on just the date in column j, but I can't figure out how to get the status involved.

    1. Hi Kayla,

      Please use the below function,

      =IF(AND(J14>TODAY(),K14="Open"),"On Track",IF(AND(J14>TODAY(),K14="Close"),"Completed",IF(AND(J14<TODAY(),OR(K14="Open",K14="Close")),"Delayed","")))

      Thanks,
      Prabath

  50. Hi,

    I have scenario,

    I am trying to get all the blank fields from the column, for ex- if I have this column.

    13 years 6 months 0 days
    34 years 5 months 0 days
    10 years 9 months 0 days
    14 years 0 days
    6 years 8 months 0 days
    5 years 11 months 0 days
    23 years 6 months
    6 years 9 months 0 days
    8 years 9 months 0 days
    30 years 9 months 0 days
    7 years 7 months 0 days
    15 years 4 months 0 days
    45 years 3 months 0 days
    40 years 10 months 0 days
    33 years 2 months 0 days

    13 years 9 months 0 days
    30 years 2 months 0 days
    29 years 10 months 0 days

    25 years 6 months 0 days
    14 years 9 months 0 days
    41 years 3 months 0 days
    10 years 11 months 0 days
    30 years 3 months 0 days
    15 years 11 months 0 days
    27 years 0 days 6 years

    As you can the column has the blank fields, I need to get the blank fields out from the column, also it has "NUM" error and 0, which I also need need to take out.

    Can you please tell me how to construct the formula for that.

    Thanks,
    Saurabh

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