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 22. Total comments: 2534

  1. I have struggled for 2-days with no avail.
    If A1>87508750<=12000,(A2=1)*("8000"))

    Your assistance would be greatly appreciated

    1. Hi!

      Sorry, I am now sure I can follow you. This condition (A1 is greater than 87508750 and less than or equal to 12000) can never be met, probably you mean the opposite?

      Anyway, I think it should be something like this:

      =IF(AND(condition1, condition2), value_if_true, falue_if_false)

      If you can clarify the conditions and values to return, I believe we will get it to work.

  2. I am giving students a baseline test and a post-test, seeing the difference within the 2. After the baseline test we come up with a growth target. It's the percentage increase we would like to see, and there are 4 tiers.
    So if the student scores:

    6%-18% should increase 40%
    19%-31% should increase 30%
    32%-45% should increase 20%
    46%-60% should increase 10%

    I would want a formula to take a baseline score between 6-60 and know what percent increase it should have.
    I tried =IF(B10>=46,"10",IF(B10>=32,"20",IF(B10>=19,"30","40")))
    It comes up Err:509

    Seems like the pieces are there, but still not working.
    Thanks in advance.

    1. Hi Tim,

      The formula looks correct to me and works just fine in my Excel, except that it returns text strings rather than numbers because you enclosed them in double quotes. If this is okay for you, then it's okay : )

      A quick search in google revealed that error 509 occurs when referencing cells in external Excel books. Is this the case?

      1. The formula doesn't work in Excel online, OpenOffice, or Google Sheets.

        Here is the formula: (my reference cell is cell B10)

        =IF(IF(B10>=46,10,(B10>=32,20,IF(B10>=19,30,40))))

        I did manage to use another function for a yes/no IF function:

        =IF(SUM(D10-B10)>=C10,"YES","NO")

        1. Tim,

          This IF formula is different from what you originally posted and incorrect. Please notice double IF in the beginning and missing IF before (B10>=32,...

          Copying/pasting makes a mess of formulas sometimes. Anyway, I've just tried the fixed formula in Excel Online and it worked fine:

          =IF(B10>=46, 10, IF(B10>=32,20, IF(B10>=19,30,40)))

      2. Thank you for the speedy response. I got rid of the " and it now says Err: 509. I am running openoffice instead of Office. Would that be an issue? Ideally, I would like to share with my fellow teachers in Google Drive. Gonna see if it works in that program. Are there spaces at all in the formula?

        1. Sorry, I have no experience with OpenOffice and very little with google sheets, and have no idea how formulas work there.

          1. I figured it out, Here is the formula:

            =IF(C2<=18,40,IF(C2<=31,30,IF(C2<=45,20,10)))

            It works for Excel, OpenOffice, Google Sheets.

  3. What forumla do I use if I want it to show:
    If Col A has a date entered, then Col B must show "Send email"?

    1. Hi Claire,

      Try this one:
      =IF(A1<>"", "Send email", "")

      1. I thought it was working, but it's making all the cells say "Send email" regardless of whether a date is input in Col A

        1. Not to worry, got it sorted using =IF(A2<TODAY(),IF(A2="","Check value","Send Email"),"Check value")

      2. Thank you so much!

  4. Hello. here's a quick summary of what I have and am trying to do. Have a "Due date" column, "Sold date" column, "Invoice number" column. Need another column that lets me know the status of each line.

    I would like to know the formula needed to return the following 3 options: say "complete" when there is a value in the invoice column (regardless of what other columns say); say "invoice required" when the due date has passed, there is no sold date entered and no invoice number entered; say "upcoming" when due date has not passed, no sold date entered and no invoice number entered. Thanks for the help.

    1. Hi Jordan,

      I think the following formula should work a treat:

      =IF(C2<>"", "complete", IF(AND(A2<TODAY(), B2="", C2=""), "invoice required", IF(AND(A2>=TODAY(), B2="", C2=""), "upcoming", "")))

      Where A2 is the "Due date" column, B2 is "Sold date" and C2 is "Invoice number".

      1. Hi,

        Thank you, the formula worked great. However, I need some additional help now. I have a fourth column that drives the original columns (Due date, Sold date, Invoice number) called "transfer date". This is the column that starts my whole sheet. Right now if this transfer date column is blank it causes the column I entered your equation into to read "Invoice Required". I'd like it to be blank until data starts getting entered into it's row. Can you let me know what I need to add to your original formula to make this happen? I would like to add this concept to other columns as well. For instance, my Due date column is calculated off the Transfer date column as well and would like it to only calculate a value if something is entered.

        Thanks very much, you've been very helpful.

        Jordan

        1. remove the "Invoice required" but leave the "".

  5. if I have two condition and I want a column parallel to that to be picked up in case both condition match what formula should I use. for eg if person "A" with id "1" as done several "training" in different "dates" so I want a formula to catch the date if the id and training name match

      1. hello Svetlana,
        Simple Vlookup can match only one condition. but I have two condition. The data consist of person name , ID number and training name they have Attended with training date (There are 100 different training) . so i want to pull training date if the persons id and training name matches.

  6. hi, I am new to Excel and finding it rather difficult to understand. I would like a column in the my spreadsheet to show that if the total is cell D is 0 then it to say paid. If the total in cell D is more than 0 than it say unpaid. Is there a formula that would enable this? Is there also any way to change the colour of the cell to green if it has been paid and red if it still unpaid. Would this need to be added into the original formula and if so then how?

    Thanks
    Gillian

    1. =IF(D1<=0,"Paid","Unpaid")

      To then change the colour of the cell, you would use conditional formatting by creating a new rule and then selecting "format only cells that contain" and changing the rules and conditions in the box below this.

      Hope this makes sense.

  7. HI!!

    I have been searching for hours how to find the IF function for:

    IF B4 is less than 40, 0. If B4 is greater than 40, the number OVER 40.

    So if the number is 47, how can i get the formula to put 7? Being that 47 is 7 more than 40.

    1. Hi Alan,

      You simply put the mathematical expression that calculates the difference in the value_if_false argument, like this:

      =IF(B4<40, 0, B4-40)

      1. Svetlana could you help with this please?

        I am using the below array formula to count how many unique values there are amongst a list of data based on certain arguments. It works when referencing a cell in this case it's A4 and A4 contains the text Proactive Contact. The problem I have is we now have new fields in the list which could be Proactive Contact 2, Proactive Contact 3, all the way up to 15. I therefore instead of referencing A4 want the argument to be "contains the text proactive contact" is this possible?

        Current Formula Used

        =SUM(IF(FREQUENCY(IF('BG Proactive Programme 11'!$C:$C"",IF('BG Proactive Programme 11'!$G:$G=A4,IF('BG Proactive Programme 11'!$H:$H=A2,MATCH("~"&'BG Proactive Programme 11'!$C:$C,'BG Proactive Programme 11'!$C:$C&"",0)))),ROW('BG Proactive Programme 11'!$C:$C)-ROW('BG Proactive Programme 11'!$C$2)+1),1))

      2. TYTYTYTYTYTYY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  8. Sorry, made an error in the formulas I have tried.
    I meant:
    =IF(L2 ="Deletion"; "0"; "=F2-E2")

    1. Hi Marcia,

      You were almost there :)

      Of course, you can use other functions and mathematical operations in the value_if_false and value_if_true arguments. You just don't want either = or "" there. When you enclose something in double quotes, Excel treats it as a text string rather than a cell reference of arithmetic operation, so it returns exactly what you typed.

      Once you remove those two, the formula works just fine:

      =IF(L2 ="Deletion"; 0; F2-E2)

      If you'd rather have an empty cell instead of 0 when the condition is met, then you put it like this:

      =IF(L2 ="Deletion"; ""; F2-E2)

      Also, there's a little mismatch between your description "display the sum of two other cells" and the formula :) If you really wanted the sum, then it should be:

      =IF(L2 ="Deletion"; 0; F2+E2)

      Hope this helps.

  9. I'm sorry. My entire formula did not copy over. Here is entire IF formula:

    =IF(A2<1,"0",IF(A2=501,"=A2")))

    What I am trying to do is say if the A2 cell amount is larger than 501, then the actual number from cell A2 be shown. How do I do that? When I put this in, the answer I get is, of course, A2 and not the actual number. Please help!

    1. Hi Lorraine,

      Your formula is almost correct, you just don't need to enclose cell references and numbers in double quotes:

      =IF(A2<1,0,IF(A2=501,A2))

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