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

  1. Hello!
    My name is shabir
    I'm looking for the formula that can count only 7 lowest grades out of range.
    Example there are 12 subjects which students should sit for in every term. So to rank their positions a teacher needs to pick only 7 out of 12 subjects of which a student has performed better. Regarding that A = 1, B= 2, C= 3, D=4, And F =5
    Please help

  2. How do you put multiple values from different sheets to return those values separated by commas into one cell?

    Sheet #1 is the Summary page with two columns: Column A: Names (A1: A28); Column B: (where I want to have all the attended conference names in the row w/ its respective name.)

    Sheet #2, #3, #4, etc are the conference sheets that contain a table with Column A: Names (A1: A28), and the Conference name in cell C6.

    What formula works to say, "If the name from Sheet #1 is listed in Sheet #2 (column A), then bring the conference name in cell C6 of that specific sheet, and add it to Sheet #1/Column B/row of that particular Name...and continue to concatenate all of these values into the same cell, separated by a comma.

    Thank you for your help.

    Regards,
    D$

    1. Hello!
      I believe the following formula will help you solve your task:

      =CONCATENATE(IF(ISNUMBER(MATCH(A3,Sheet2!A1:A28,0)),Sheet2!C6,""),IF(ISNUMBER(MATCH(A3,Sheet3!A1:A28,0)),Sheet3!C6,""),IF(ISNUMBER(MATCH(A3,Sheet4!A1:A28,0)),Sheet4!C6,""))

      =TEXTJOIN(",",TRUE,IF(ISNUMBER(MATCH(A3,Sheet2!A1:A28,0)),Sheet2!C6,""),IF(ISNUMBER(MATCH(A3,Sheet3!A1:A28,0)),Sheet3!C6,""),IF(ISNUMBER(MATCH(A3,Sheet4!A1:A28,0)),Sheet4!C6,""))

      You can learn more about MATCH function in Excel in this article on our blog.
      Please have a look at this article — TEXTJOIN function in Excel to merge text from multiple cells

  3. Hi,
    Is this formula correct? I need to find a value in another sheet
    =IF(MATCH(A1,Sheet1!A:A,0),"TAGAYTAY", IF(MATCH(A1,Sheet1!B:B,0), "DASMA", IF(MATCH(A1,Sheet1!C:C,0), "NAIC")))

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.
      Describe in detail what problem you have, and I will try to help you.

    2. on the 1st IF statement the value of A1 is appearing, but when I tried on the 2nd IF statement the value returns #N/A

  4. Hi,

    Please help me in this IF function formula. So far I cannot get it work.

    IF A2=0% OR B2=0% OR C2=0% OR D2=0%, then F2 is equal to (A2+B2+C2+D2)/3,

    IF A2=0% AND B2=0%, then F2 is equal to (A2+B2+C2+D2)/2,

    IF B2=0% AND C2=0%, then F2 is equal to (A2+B2+C2+D2)/2,

    IF C2=0% AND D2=0%, then F2 is equal to (A2+B2+C2+D2)/2,

    IF A2=0% AND D2=0%, then F2 is equal to (A2+B2+C2+D2)/2,

    IF A2=0% AND B2=0% AND C2=0%, then F2 is equal to (A2+B2+C2+D2)/1,

    IF A2=0% AND B2=0% AND D2=0%, then F2 is equal to (A2+B2+C2+D2)/1,

    IF A2=0% AND C2=0% AND D2=0%, then F2 is equal to (A2+B2+C2+D2)/1,

    IF B2=0% AND C2=0% AND D2=0%, then F2 is equal to (A2+B2+C2+D2)/1,

    Thanks!

    1. Hello!
      Please re-check the article above since it covers your task.
      For example,

      =IF(OR(A2=0,B2=0,C2=0,D2=0),A2+B2+C2+D2)/3

  5. Hi Boss,

    Need your support to correct the formula for me.

    =if(RC[-5]=7,RC[-1]>"00<"07:01","Achieved","Not Acheived")

  6. Hi! I need help on this:
    this is for an exam..

    Correct answer: Good
    If your answer is "no good", "hold" or "for reworks" the result is STRICT.

    Correct answer: No good
    If your answer is "good", "hold" or "for reworks" the result is SWEET.

    Correct answer: "Hold"
    If your answer is "good" or "for reworks" the result is SWEET then STRICT if "no good".

    Correct answer is: For reworks
    If your answer is "good" the result is SWEET then STRICT if "no good" or "hold".

    Thank you!

  7. Hello,

    I urgently need help here, how would I format:

    If A1 is yes then B1 cell is color green with text "YES" or if A1 is no then cell B1 is red with text "NO"
    Kind of like an attendance deal.

    Also is there a way to say: if any in A1-A10 is YES then cell A12 is color green (no text) but if there is any NO then A12 is color red (no text).

    I can't figure out the color element so I'd like to see both ways to see which one will be easier to implement.

    Thanks in advance!!
    Linda

  8. here is what I'm trying to solve:

    Cell could have the following data in it: apple, orange, grape, celery

    Looking for an IF statement that would drop into another cell either fruit or veggie.

    IF apple, orange, grape = fruit, if celery = veggie

    hope that makes sense. Thanks for your help.

    1. Happy to report that I figured it out!!! Thanks!

  9. hello I need help here
    If column A2 is less or equal to 70 and the sum for cells D2:AA2 is 50 I want it to say "more effort Please" for else to leave it blank. I have tried this but it is not working
    IF((AND(A2<=70, SUM(D2:Aa2=50)), More effort please", " "))
    Thanks

      1. Thank you so much it worked very well

  10. I want to color a cell based on whether it is a date or not. Eg:if A1 has a date, E1 should be green otherwise, no color.
    The closest I've got is using 'ISNUMBER' function. But this will color E1 even if I randomly put a number in A1.Is there anyway I can specify it to date?

    1. Hello!
      I think that the cell in which the date is written must have a date format. Therefore, I recommend using the CELL function and checking what format the cell has.

      =CELL("format",F3)

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

      1. I got it.. thank you?

  11. Hi,

    I'm trying to setup an if statement needing both the and & or functions.

    The OR statement is =IF(OR(Recruits="Yes",Refresh="Yes"),1,0)
    The AND statement will be if Eng = Yes, and IF(OR(Recruits="Yes",Refresh="Yes")

    I can get both to work individually, but not together, is this possible?

    I have created a matrix to see all possible outcomes, to test (Below)

    Eng Recruits Refresh
    Yes Yes Yes
    Yes Yes
    Yes
    Yes
    Yes
    Yes

    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(AND(A2="Yes",OR(B2="Yes",C2="Yes")),1,0)

  12. Related text need to display in 1st table from the 2nd table, If we write a remarks in the 2nd table following and related text in the 1st table -- can any one write the if formula for this

  13. If age is between 18 and 56 and Sex is either "M" or "F", remark is "Qualified"

    2. what is the formula? If age is between 18 and 56 and Sex is either "M" or "F", remark is "Qualified"

  14. What is the formula"if function" if you are getting the valedictorian, salutatorian in a class with the basis of ranking?

  15. Hi, I notice many IF scenarios above, but none that fit my scenario where I am also using a hyperlink. Any chance you can assist with the answer? Below is an example of what I am doing. The only thing I am trying to add is when D12 & D13 = other number combinations, I want that to reference a different hyperlink (image). Is it possible to add multiple IF's and hyperlink's in a single formula?

    =IF(AND(D12=2,D13=1),HYPERLINK("https://test1.jpg","View Layout"),"")

    I know this formula is wrong, but it depicts what I am trying to do with as many conditions as I need:

    =IF(AND(D12=2,D13=1),HYPERLINK("https://test1.jpg","View Layout"),IF(AND(D12=5,D13=2),HYPERLINK("https://test2.jpg","View Layout"),"")...so on and so on

    Thanks,
    Kyle

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =HYPERLINK(IF(AND(D12=2,D13=1),"https://test1.jpg", IF(AND(D12=5,D13=2),"https://test2.jpg","")), IF(AND(D12=5,D13=2),"view2", IF(AND(D12=2,D13=1),"view1","")))

      You can learn more about HYPERLINK function in Excel in this article on our blog.

  16. I have used the formula =IF(P2="V13-1X-BLU-1",N2+1457), so when the cell contains the text V13-1X-BLU-1 it adds 1457 days to the date (N2 contains date). Is it possible expand the formula to recognise more text (V9-2X-BLU-1) and make different additions (+804) ?

  17. I hope someone could help me to condition, where I can have a column where it would either say “Complete” and “Incomplete” to reflect overall completion of requirements? The value is either Yes or No. If all answered 'YES' it will show "complete" and if any "No" answered "incomplete

  18. Hello! I'm trying to do a multiple conditional formula and I'm having trouble getting it to work...
    I'm trying to make something like this:
    If A is TRUE and B is TRUE, the formula should be something like AVERAGE(A;B)
    If A is TRUE and B is FALSE, then it should be =A
    If A is FALSE and B is TRUE, then it should be =B
    If A is FALSE and B is FALSE, =0
    I wanted to do it with multiple conditions, like A, B, C...
    How can I make it work? I tried everything I knew.
    Thanks!

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Use a formula like this:

      =IF(AND(A1=TRUE,B1=TRUE),AVERAGE(A1:B1), IF(AND(A1=TRUE,B1=FALSE),A1, IF(AND(B1=TRUE,A1=FALSE),B1, IF(AND(A1=FALSE,B1=FALSE),0,""))))

  19. I NEED TO CREATE A FUNCTION:

    =IF (IC25>IA25, "URGENT", "OK")

    BUT I ALSO WANT IT TO LOOK AT COLUMN - ID IS GREATER THAN 1 THAT IT CREATES "ON PO"

    1. Hello!
      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(ID25>1,"ON PO",IF(IC25>IA25,"URGENT","OK"))

  20. Please provide formula :
    IF B4=290,B4=2449,B4=250 C2="",C3+"", if True " Please provide this information" if False the data in E1.

  21. Found really useful thank you!

  22. I need a formula for below conditions (urgent required):

    01. if (a1 or b1 or c1 = "P", 1,0
    02. (i) IF(A1:C1)="P", "THREE",0 (three cells value are "P")

    (ii) IF (A1 AND B1="P" AND C1="") OR (A1 AND C1="P" AND B1="") OR (B1 AND C1="P" AND A1=""), "TWO",0
    (any two cells are "P" from three cell)

    (iii) IF (a1="p" and b1 or c1="") OR a1 and b1 ="" and c1="p") OR (a1 and c1="" and b1="p"), "ONE",0
    (only any one cell is "P" from three cells)

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

  23. =TEXTJOIN("/",TRUE,IF(K2>=45,45,IF(K2=40,"35/5",IF(K2=30,"25/5",IF(K2=20,"10/10",IF(K2=15,"10/5",IF(K2=0,"",K2)))))

    Could someone explain what is wrong with formula or if I'm using the wrong mechanism.

    This is the error I am receiving "Wrong number of arguments to IF. Expected between 2 and 3 arguments, but got 7 arguments."

    Any help is appreciated, thanks.

    1. Hello!
      Please try the following formula:

      =TEXTJOIN("/",TRUE,IF(K2>=45,45,IF(K2=40,"35/5",IF(K2=30,"25/5",IF(K2=20,"10/10",IF(K2=15,"10/5",IF(K2=0,"",K2)))))))

      Hope this is what you need.

  24. i am using formula which is below mentioned but the result is for "yes" ok but for "#n/a" result is same #n/a but i need to show the result is "NO" which is base on formula.

    example: =IF(I1="#N/A","NO","YES")

    regards,
    ABAS

  25. Hello! I need a formula to display a scholarship.

    These are the conditions:

    if final_grade < 8 print “No receive”;

    • if 8 ≤ final_grade < 9 print 300 ;

    • if 9 ≤ final_grade < 9,5 print 400 ;

    • if final_grade ≥ 9,5 print 600 .

    of course, I have a column where final grades are placed.

    I wish I was clear with my commnet... I can't wait for an answer!

    Thank you, All the best!

    2. my difficulty is to use more conditions in "IF" syntax... I don't know how to do that...

      1. C6=GRADE
        =IF(C6<8,"NO RECEIVE", IF(C6<9, 300,IF(C6<9.5, 400, 600)))

      2. I tried to use more '' IF '' in Formula Bar ( like in Progamming for example - elseif ... ... ...) but it doesn't work and I don't think it's possible something like that.

  26. If cell C2 is greater than 50000, then give 20% discount, if less than 50000 give nil discount. Please assist me with the correct IF Function

  27. Thank you for this page. I used to work in an office and now use Excel to calculate my marks as a teacher but it's been a while since I've used the IF formula. So grateful to have found your summary!

  28. Hello, Can you please help me with the below formula , I want to set an Active, Non Active status relating an end date of a contract based on today date.
    Thank you,

  29. Hello,
    Column A has names of people ( repititions are also present )
    Column B has multiple codes names with which they are called
    example -
    column A(names) column B( code name) Column (pocketmoney) --------- sheet 1
    James Jamie ?
    James Jam ?
    nick Nicky ?
    john jonny ?
    derek D ?
    thomas Tom ?
    derek D dog ?
    thomas TIM ?

    a different sheet is same excel has data as below -- sheet 2

    Column A(fathers name) column B(mothers name) Column C ( pocket money ) Column D(code nam)
    ABC AAA 1$ Jamie
    XYZ BBB 2$ Jam
    XXX CCC 3 Nicky
    YYY DDD 4 jonny
    ZZZ EEE 5 D
    ZXZ FFF 6 TOM
    ZZY GGG 7 D Doog
    AZZ HHH 8 TIM

    My condition is as below
    If same names have multiple code names in sheet 1, then allocate the pocket money for children( column A in sheet ) based on their codes names in Column D of sheet 2

    example -
    James has 2 code names ( jamie and jam) , so the one with code name jam should get 2 $ as pocket money and jamie should get one

    i need a formula to write in column C of sheet 1 to retrieve the pocket money of the children having same name but different nick name

  30. I am creating a spreadsheet for a darts score card. I want to display "winner" in a cell when a player's score reaches zero and Loser in another cell at the same time for the opposing player. I also want to display Game on in both cells whilst the game is in progress, i.e. when both players score is greater than zero,
    I have managed to display winner when either player reaches zero, and Game on when they have a score greater than zero, but I am struggling to achieve displaying loser in the opposing cell which will still display Game on.

    1. Hi,
      Sorry, it's not quite clear what you are trying to achieve. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

  31. I required a formula

    Name factory Name invoice type Qty
    A Rawal Plant GST 478
    B AJK Plant without GST 325
    C Rawal Plant without GST 843
    D AJK Plant GST 275
    E AJK Plant GST 564
    F Rawal Plant without GST 528
    G AJK Plant without GST 362
    H AJK Plant GST 358

    Required data separately Qty
    AJK Plant GST ?
    AJK Plant without GST ?
    Rawal Plant GST ?
    Rawal Plant without GST ?

  32. I have to measure the height of the balls in a a ball mill to determine how many balls to feed example

    Say I measure 100 cm from the height in the ball mill , there is another table that I have that gives ranges that 95 cm to 100 cm you add 1 kg of balls into the mill ,

    My question is how do I add my table that gives the ranges so that when I do the measurements and put them into my excel sheet it automatically looks at the ranges and automatically tells me add 1kg or 2kg etc... not sure if you get me but if you do I just need a formula to use

  33. I know how to reference cells on other sheets, but I'd trying to point to a cell on another sheet IF the date at the beginning matches the date I input.

    For example: I have dates running down the A column on Sheet1, and total sales running down the B column also on Sheet1.

    On Sheet2, I'd like to enter a date into A1 and, on A2, I'd like it to tell me the total for that day, as already listed on Sheet1.

    ---

    Sheet1:
    Dates:
    A1 - 01/04/21
    A2 - 02/04/21
    A3 - 03/04/21
    etc.

    Total sales:
    B1 - 7
    B2 - 4
    B3 - 8
    etc.

    ---

    Sheet 1:
    B:B - I can input the total sales for the day in A:A

    Sheet 2:
    A1 - I can input any date.
    A2 - show me the total sales for that date.

    ---

    I've tried using =IF on Sheet2 and manually nesting formulas for each cell in each row I want to reference on Sheet1, instance by instance.
    Eventually, I'd end up nesting over 100 formulas in a single cell on Sheet2.

    Is there a way to do this more generally?

    1. Hello!
      If I understood the problem correctly, the SUMIF function can be used in cell A2. You can learn more about SUMIF in Excel in this article on our blog.
      I hope my advice will help you solve your task.

      1. Hi Alexander,

        That's exactly what I needed. Perfect! Thanks for that :)

  34. Hi

    I hope you can help.

    I am looking to automate a cash flow forecast using figures from a separate 'Budget' tab, so that payments are listed on the specific day that they are due.

    I need to cater for various different suppliers, some of which are paid at the end of the month and others at various days during the month.

    I have a separate column which displays the payment due date, which will be listed as '31' for end of month (to help identify those that need adjusting for shorter months), and the specific day of month for all others.

    In short, I need the budget figure 'Budget K16' to only show on the specific day confirmed on each line ('B24') for that supplier, and if not due, to show as zero/empty.

    Budget K16 = Payment amount from other tab
    B24 = Day of month that payment is due
    D3 = Date

    I have come up with the following formula to ensure that the payments are displayed on the day that they are due to be paid, but the IF statement is not working.

    =IF(AND(OR(B24="31"), AND(D3=EOMONTH),'Budget '!$K$16,0),OR(DAY(D3)=$B$24,'Budget '!$K$16,0)

    Are there any obvious errors with this? Any help would be much appreciated.

    Kind regards

    Dave

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Correct me if I’m wrong, but I believe the formula below will help:

      =IF(OR(AND(B24=31, D3=EOMONTH(MONTH(D3),0)),DAY(D3)=$B$24),$K$16,0)

      Use external links to other sheets in your workbook. I cannot do this.

  35. I have to use single cell as a condition for the whole column.. How can i use it?

    For example in the below formula i have to use cell Formula!A2 for all the cells in the column W in the workings spreadsheet.

    =IF(Workings!W2=Formula!A2,CONCATENATE("V2",".",A3),CONCATENATE("I2",".",A3))

  36. Hello,

    Needing some help with a formula that will produce my values that might have a letter at the end of it. IE.

    =IFERROR(IF(OR(VALUE(B90)7000),"",VALUE(B90)),"") this works perfectly fine when I am doing only numbers like 1000 or 4000, but when I have a 3429G, this formula will obviously leave my cell blank. I need to get my cells that might have inventory letters and numbers together to post in that cell. Is there a formula to help identify if there is text within numbers?

    1. Hi,
      If there are no letters in a cell, it can be converted to a number. If this is not possible, then there are letters in the cell.
      For example:

      =IF(ISNUMBER(--(B1)),"Number","Text")

      I hope my advice will help you solve your task.

    2. Sorry it messed the formula up for some reason. Here is my current formula:

      =IFERROR(IF(OR(VALUE(B86)7000),"",VALUE(B86)),"")

  37. I'm looking to do a mail merge from an excel sheet to a word document/contract. I need a formula that will basically say any time you see "One (1)" in the document then "First (1st)" will appear throughout where needed. If "Two (2)" is written, then "Second (2nd)" would appear when needed. I would like to have this up to "Ten (10)" if possible.

    Any ideas for a formula?

  38. However I need to add EQ07 in to the above formula where as the condition is different

    IF(A5="EQ07",IF(AF<=70,"70")

    I need to have only one formula that reads value of Column A and accordingly reviews the condition and provides result in column B

    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(A5="EQ07",IF(AF<=70,70,""),"")

      Please have a look at this article — Nested IF in Excel – formula with multiple conditions

  39. Good Day! Need assist!
    I have a formula: =IF(I2>64,IF(J2<=$O$20,TRUE),IF(I2$O$20,TRUE),IF(I2>64,IF(J2>$O$20,FALSE),IF(I2<64,IF(J2<=$O$20,FALSE)))))
    O20 is a particular criteria near the table that I am changing.
    Currently I am receiving everything correctly, where True is that my system results (column J) predicted the results from column I and False appears when the system provided a wrong result. However, there is an exception that I need to add IF(I2<64,IF(J2<=$O$20,FALSE) I want to see in this case in the cell "EXCEP"
    Please, comment if I do not described the question correctly?

    1. Hello!
      Try the following formula:

      =IF(I2>64,IF(J2<=$O$20,TRUE,
      IF(I2<$O$20,TRUE,
      IF(I2>64,IF(J2>$O$20,FALSE,
      IF(I2<64,IF(J2<=$O$20,FALSE)))))),IF(J2<=$O$20,FALSE))

      I'm not sure if this will work. There is already a condition IF(I2<64 in your formula.
      I can’t check the formula, because I don’t know the conditions and I don’t have the source data.

  40. HELLO i need help please.

    i need a formula that says
    if in a cell there is the word- red then return a 3 if it says blue then return a 2 and if yellow then return a 1

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(ISNUMBER(SEARCH("red",B1,1)),3, IF(ISNUMBER(SEARCH("blue",B1,1)),2, IF(ISNUMBER(SEARCH("yellow",B1,1)),1,"")))

      You can learn more about SEARCH function in Excel in this article on our blog.

  41. im looking for a formula for 2 pages to look up if the account number is the same it would add up the totals in the totals column for that account number and give me one total Can you help please.

  42. Please share if its says YES than add no value anf if it says NO than add value formula in excel
    AS EXAMPLE.
    A1 A2 A3
    YES 500 FORMULA PLEASE
    NO 100

  43. Hello to all,
    I really need your help about this formula. I've tried everything I know about excel...

    When I use this formula to check the field A30 (could be a date OR text Always On):
    =IF(OR(A30="Always On";A30>=TODAY());"ACTIVE";"NOT ACTIVE")
    If I put a date in A30, the result is correct, ACTIVE if higher or NOT ACTIVE if lower.
    But with any text in A30, it gives me always ACTIVE, instead of only recognizing Always On.

    I have tested the same formula but choosing different fields (A30 and B30):
    =IF(OR(A30="Always On";B30>=TODAY());"ACTIVE";"NOT ACTIVE")
    And in this case, it recognize either the date AND the correct or wrong text.

    Do you know why it is happening when it is the same field?

    Thank you for your help!

    1. Hello!
      Add a condition to the formula: if the date is checked, then A30 must be a number.

      =IF(OR(A30="Always On",AND(ISNUMBER(A30),A30>=TODAY())), "ACTIVE","NOT ACTIVE")

      I hope it’ll be helpful.

  44. Hi,

    I'm newbie to excel, I hope you could help me, basically I'm trying to get the formula if two conditions are met
    example: If A1="apple" and A4 contains %(eg.12345%Mango), then it will sum the C1/B4 and do nothing if not.

    Appreciate your response.

    1. Hello!
      What does the “sum the C1/B4” phrase mean?
      This formula shows the fulfillment of your conditions.

      =IF(AND(A1="apple",ISNUMBER(SEARCH("%",A4,1))),"Yes","No")

      You can write other values or formulas instead of Yes and No.

  45. Hello! I'm pretty new to excel but I'm trying to get a status using the equation below. There are three different options that can happen "completed", "not started", and "waived". What I'm trying to figure out is how to get "completed" and "waived" to come out across a row as "True" but if there were to be a "not started" for example in cell E2 then for it to show the status as "False".

    =IF(OR(AND(D2="completed",D2="waived"),AND(E2="completed",E2="waived"),AND(F2="completed",F2="waived"),AND(G2="completed",G2="waived"),AND(H2="completed",H2="waived"),AND(I2="completed",I2="waived")),"True","True")

    Thank you!

  46. Hi How would i set up formatting? using an If statement for example
    if A1:A3 = yes color whole row green (A1:BE1)

    thank you in advance!

  47. Hello!

    Trying to figure out how to do if statements to add up to a number between 1 and 4 if they hit certain if statements and then have that number multiple by 200

    If( B1>=A1,0,1) + ( If B2>=A2,0,1) + (B3>=A3,0,1) + (B4>=B4,0,1) .....Then depending on how many 1's that adds up to that sum would be multiplied by 200

    1. Hello!
      If I understand you correctly, then you can use any of these formulas:

      =(SUM(IF(B1>=A1,0,1),IF(B2>=A2,0,1),IF(B3>=A3,0,1),IF(B4>=B4,0,1)))*200

      =(SUM(--(B1<A1),--(B2<A2),--(B3<A3),--(B4<B4)))*200

      =((B1<A1)+(B2<A2)+(B3<A3)+(B4<B4))*200

      Hope this is what you need.

  48. Hi ,

    Could you please help me to find formula

    A | B | C | D | E | F | G | H | I | J | K | L | M |
    WARD|Ward|Description|Average|Ward.|Wardwise|Total|RES |RES|COM |COM|INDUSTRY|INDSTRY|
    1 |1 |RES |0.90% |1 |0.80% |3 |0.90%|2 |0.60%|1 | |
    2 |1 |COM |0.60% |2 |0.60% |1 | | | | |0.60% | 1
    3 |4 |RES |1.00% |3 |1.50% |1 | | |1.50%|1 | |
    4 |5 |RES |1.10% |4 |1.00% |1 |1.00%|1 | | | |
    5 |9 |RES |0.30% |5 |1.00% |2 |1.00%|2 | | | |
    6 |2 |INDUSTRY |0.60% |6 | | | | | | | |
    7 |1 |RES |0.90% |7 | | | | | | | |
    8 |9 |COM |0.30% |8 | | | | | | | |
    9 |5 |RES |0.90% |9 |0.30% |2 |0.30%|1 |0.30%|1 | |
    10 |3 |COM |1.50% |10 | | | | | | | |

    column A,B,C,D (data) (i wanted formula formula to sort column A-D in ascending )
    Column F - ward wise average (used formula =IFERROR(AVERAGEIF($B$3:$B$12,(A2),$D$3:$D$12),"") )
    Column H, J, L - used formula - IFERROR(AVERAGEIFS($D$3:$D$12,$B$3:$B$12,A2,$C$3:$C$12,$I$1),"")

    i have used data - sort in coulmn G,I,K,M but i want formula to sort wardwise-RES, wardwise-COM, etc.)

    in Column N - wanted highest average repeated in column D , and total highest average counts repeated in Column O

    Please help me in find the solution ........ plz need help

  49. cell A1 can have value "ok", "nok", or "n/a".
    same cell B1 can have value "ok", "nok", or "n/a".

    How can I in cell C1 return:

    ok if both A1andB1 are ok.
    nok if either is nok.
    ok if A1orB1 is ok and the other is n/a.
    n/a if both are n/a.

    Below formula returns all except n/a.

    =IF(OR(AND(DD3527="ok", DR3527="ok"),AND(DD3527="n/a",DR3527="n/a")), "ok", "nok")

    1. Hello!
      Please try the following formula:

      =IF(OR(A1="nok",B1="nok"),"nok",IF(OR(A1="ok",B1="ok"),"ok",IF(AND(A1="na",B1="na"),"na","")))

      Hope this is what you need.

      1. You are AWESOME!! Thank you!!

  50. Hi - can someone help me write this in a Boolean "IF" statement.

    IF the years of experience is "0" or less than 5 years, the raise is 2%
    IF the years of experience is 5 years and less than 10 years, the raise is 3%
    IF the years of experience is 10 years and less than 14 years, the raise is 4%
    IF the years of experience is 13 years and less than 18 years, the raise is 5%
    IF the years of experience is greater than 17 the raise is "0"

    Tanya

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      Your conditions 4 and 5 contradict each other.

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