Excel IF statement with multiple conditions

The tutorial shows how to create multiple IF statements in Excel with AND as well as OR logic. Also, you will learn how to use IF together with other Excel functions.

In the first part of our Excel IF tutorial, we looked at how to construct a simple IF statement with one condition for text, numbers, dates, blanks and non-blanks. For powerful data analysis, however, you may often need to evaluate multiple conditions at a time. The below formula examples will show you the most effective ways to do this.

How to use IF function with multiple conditions

In essence, there are two types of the IF formula with multiple criteria based on the AND / OR logic. Consequently, in the logical test of your IF formula, you should use one of these functions:

  • AND function - returns TRUE if all the conditions are met; FALSE otherwise.
  • OR function - returns TRUE if any single condition is met; FALSE otherwise.

To better illustrate the point, let's investigate some real-life formulas examples.

Excel IF statement with multiple conditions (AND logic)

The generic formula of Excel IF with two or more conditions is this:

IF(AND(condition1, condition2, …), value_if_true, value_if_false)

Translated into a human language, the formula says: If condition 1 is true AND condition 2 is true, return value_if_true; else return value_if_false.

Suppose you have a table listing the scores of two tests in columns B and C. To pass the final exam, a student must have both scores greater than 50.

For the logical test, you use the following AND statement: AND(B2>50, C2>50)

If both conditions are true, the formula will return "Pass"; if any condition is false - "Fail".

=IF(AND(B2>50, B2>50), "Pass", "Fail")

Easy, isn't it? The screenshot below proves that our Excel IF /AND formula works right: Excel IF statement with multiple AND conditions

In a similar manner, you can use the Excel IF function with multiple text conditions.

For instance, to output "Good" if both B2 and C2 are greater than 50, "Bad" otherwise, the formula is:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad") Excel IF function with multiple text conditions

Important note! The AND function checks all the conditions, even if the already tested one(s) evaluated to FALSE. Such behavior is a bit unusual since in most of programming languages, subsequent conditions are not tested if any of the previous tests has returned FALSE.

In practice, a seemingly correct IF statement may result in an error because of this specificity. For example, the below formula would return #DIV/0! ("divide by zero" error) if cell A2 is equal to 0:

=IF(AND(A2<>0, (1/A2)>0.5),"Good", "Bad")

The avoid this, you should use a nested IF function:

=IF(A2<>0, IF((1/A2)>0.5, "Good", "Bad"), "Bad")

For more information, please see IF AND formula in Excel.

Excel IF function with multiple conditions (OR logic)

To do one thing if any condition is met, otherwise do something else, use this combination of the IF and OR functions:

IF(OR(condition1, condition2, …), value_if_true, value_if_false)

The difference from the IF / AND formula discussed above is that Excel returns TRUE if any of the specified conditions is true.

So, if in the previous formula, we use OR instead of AND:

=IF(OR(B2>50, B2>50), "Pass", "Fail")

Then anyone who has more than 50 points in either exam will get "Pass" in column D. With such conditions, our students have a better chance to pass the final exam (Yvette being particularly unlucky failing by just 1 point :) Excel IF function with multiple OR conditions

Tip. In case you are creating a multiple IF statement with text and testing a value in one cell with the OR logic (i.e. a cell can be "this" or "that"), then you can build a more compact formula using an array constant.

For example, to mark a sale as "closed" if cell B2 is either "delivered" or "paid", the formula is:

=IF(OR(B2={"delivered", "paid"}), "Closed", "")

More formula examples can be found in Excel IF OR function.

IF with multiple AND & OR statements

If your task requires evaluating several sets of multiple conditions, you will have to utilize both AND & OR functions at a time.

In our sample table, suppose you have the following criteria for checking the exam results:

  • Condition 1: exam1>50 and exam2>50
  • Condition 2: exam1>40 and exam2>60

If either of the conditions is met, the final exam is deemed passed.

At first sight, the formula seems a little tricky, but in fact it is not! You just express each of the above conditions as an AND statement and nest them in the OR function (since it's not necessary to meet both conditions, either will suffice):

OR(AND(B2>50, C2>50), AND(B2>40, C2>60)

Then, use the OR function for the logical test of IF and supply the desired value_if_true and value_if_false values. As the result, you get the following IF formula with multiple AND / OR conditions:

=IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), "Pass", "Fail")

The screenshot below indicates that we've done the formula right: IF with multiple AND & OR statements

Naturally, you are not limited to using only two AND/OR functions in your IF formulas. You can use as many of them as your business logic requires, provided that:

  • In Excel 2007 and higher, you have no more than 255 arguments, and the total length of the IF formula does not exceed 8,192 characters.
  • In Excel 2003 and lower, there are no more than 30 arguments, and the total length of your IF formula does not exceed 1,024 characters.

Nested IF statement to check multiple logical tests

If you want to evaluate multiple logical tests within a single formula, then you can nest several functions one into another. Such functions are called nested IF functions. They prove particularly useful when you wish to return different values depending on the logical tests' results.

Here's a typical example: suppose you want to qualify the students' achievements as "Good", "Satisfactory" and "Poor" based on the following scores:

  • Good: 60 or more (>=60)
  • Satisfactory: between 40 and 60 (>40 and <60)
  • Poor: 40 or less (<=40)

Before writing a formula, consider the order of functions you are going to nest. Excel will evaluate the logical tests in the order they appear in the formula. Once a condition evaluates to TRUE, the subsequent conditions are not tested, meaning the formula stops after the first TRUE result.

In our case, the functions are arranged from largest to smallest:

=IF(B2>=60, "Good", IF(B2>40, "Satisfactory", "Poor"))

Naturally, you can nest more functions if needed (up to 64 in modern versions). Nested IF statement in Excel

For more information, please see How to use multiple nested IF statements in Excel.

Excel IF array formula with multiple conditions

Another way to get an Excel IF to test multiple conditions is by using an array formula.

To evaluate conditions with the AND logic, use the asterisk:

IF(condition1) * (condition2) * …, value_if_true, value_if_false)

To test conditions with the OR logic, use the plus sign:

IF(condition1) + (condition2) + …, value_if_true, value_if_false)

To complete an array formula correctly, press the Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.

For example, to get "Pass" if both B2 and C2 are greater than 50, the formula is:

=IF((B2>50) * (C2>50), "Pass", "Fail") IF array formula with multiple AND conditions

In my Excel 365, a normal formula works just fine (as you can see in the screenshots above). In Excel 2019 and lower, remember to make it an array formula by using the Ctrl + Shift + Enter shortcut.

To evaluate multiple conditions with the OR logic, the formula is:

=IF((B2>50) + (C2>50), "Pass", "Fail") IF array formula with multiple OR conditions

Using IF together with other functions

This section explains how to use IF in combination with other Excel functions and what benefits this gives to you.

Example 1. If #N/A error in VLOOKUP

When VLOOKUP or other lookup function cannot find something, it returns a #N/A error. To make your tables look nicer, you can return zero, blank, or specific text if #N/A. For this, use this generic formula:

IF(ISNA(VLOOKUP(…)), value_if_na, VLOOKUP(…))

For example:

If #N/A return 0:

If the lookup value in E1 is not found, the formula returns zero.

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), 0, VLOOKUP(E1, A2:B10, 2, FALSE))

If #N/A return blank:

If the lookup value is not found, the formula returns nothing (an empty string).

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "", VLOOKUP(E1, A2:B10, 2, FALSE))

If #N/A return certain text:

If the lookup value is not found, the formula returns specific text.

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "Not found", VLOOKUP(E1, A2:B10, 2, FALSE)) If #N/A error in VLOOKUP

For more formula examples, please see VLOOKUP with IF statement in Excel.

Example 2. IF with SUM, AVERAGE, MIN and MAX functions

To sum cell values based on certain criteria, Excel provides the SUMIF and SUMIFS functions.

In some situations, your business logic may require including the SUM function in the logical test of IF. For example, to return different text labels depending on the sum of the values in B2 and C2, the formula is:

=IF(SUM(B2:C2)>130, "Good", IF(SUM(B2:C2)>110, "Satisfactory", "Poor"))

If the sum is greater than 130, the result is "good"; if greater than 110 – "satisfactory', if 110 or lower – "poor". Using the IF function with SUM

In a similar fashion, you can embed the AVERAGE function in the logical test of IF and return different labels based on the average score:

=IF(AVERAGE(B2:C2)>65, "Good", IF(AVERAGE(B2:C2)>55, "Satisfactory", "Poor"))

Assuming the total score is in column D, you can identify the highest and lowest values with the help of the MAX and MIN functions:

=IF(D2=MAX($D$2:$D$10), "Best result", "")

=IF(D2=MAX($D$2:$D$10), "Best result", "")

To have both labels in one column, nest the above functions one into another:

=IF(D2=MAX($D$2:$D$10), "Best result", IF(D2=MIN($D$2:$D$10), "Worst result", "")) Using IF together with the MIN and MAX functions

Likewise, you can use IF together with your custom functions. For example, you can combine it with GetCellColor or GetCellFontColor to return different results based on a cell color.

In addition, Excel provides a number of functions to calculate data based on conditions. For detailed formula examples, please check out the following tutorials:

  • COUNTIF - count cells that meet a condition
  • COUNTIFS - count cells with multiple criteria
  • SUMIF - conditionally sum cells
  • SUMIFS - sum cells with multiple criteria

Example 3. IF with ISNUMBER, ISTEXT and ISBLANK

To identify text, numbers and blank cells, Microsoft Excel provides special functions such as ISTEXT, ISNUMBER and ISBLANK. By placing them in the logical tests of three nested IF statements, you can identify all different data types in one go:

=IF(ISTEXT(A2), "Text", IF(ISNUMBER(A2), "Number", IF(ISBLANK(A2), "Blank", ""))) IF with ISNUMBER, ISTEXT and ISBLANK

Example 4. IF and CONCATENATE

To output the result of IF and some text into one cell, use the CONCATENATE or CONCAT (in Excel 2016 - 365) and IF functions together. For example:

=CONCATENATE("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))

=CONCAT("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))

Looking at the screenshot below, you'll hardly need any explanation of what the formula does: Using IF and CONCATENATE

IF ISERROR / ISNA formula in Excel

The modern versions of Excel have special functions to trap errors and replace them with another calculation or predefined value - IFERROR (in Excel 2007 and later) and IFNA (in Excel 2013 and later). In earlier Excel versions, you can use the IF ISERROR and IF ISNA combinations instead.

The difference is that IFERROR and ISERROR handle all possible Excel errors, including #VALUE!, #N/A, #NAME?, #REF!, #NUM!, #DIV/0!, and #NULL!. While IFNA and ISNA specialize solely in #N/A errors.

For example, to replace the "divide by zero" error (#DIV/0!) with your custom text, you can use the following formula:

=IF(ISERROR(A2/B2), "N/A", A2/B2) Using IF together with ISERROR

And that's all I have to say about using the IF function in Excel. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel IF multiple criteria - examples (.xlsx file)

Table of contents

4538 comments

  1. Please enlighten the excel formula to calculate following problems:-
    If value of C2 (in cell) is greater than 150000 than takes value greater than 150000 but less than 200000 and if value of C2 is equal to or less than 150000 ignore it.

      • Thanks for speedy replied.
        My question is that employees are submitting Income tax saving documents such as Rs.100000, Rs.134000, Rs. 200000, Rs.260000, etc. varies from one employee to other. Exemption limit admissible is Rs.150000 only irrespective of saving documents submitted by them under particular section. If employees submitted saving documents for Rs.120000, the whole amount is exempted as it is less than Rs.150000 (maximum permissible exemption) but if employee submitted saving documents for Rs.210000, his exemption is limited to Rs.150000 as per Rules. However, saving documents beyond Rs.150000 subject to a maximum of Rs.50000 can be exempted under another/difference section (beyond 150000 upto 50000). As the employee submitted saving documents of Rs.210000, he can get additional exemption of Rs.50000 in another section and excess amount of Rs.10000 to be ignore.
        My question is that an employee has submitted saving documents amounting to Rs.240000 and maximum amount of Rs.150000 is exempted in particular section and amount exceeding Rs. 150000 is to be exempted in another section subject to the limit of Rs.50000 only and rest amount to be ignore. However, if saving document submitted by employee is less than Rs.150000 the same to be ignore for exemption in another section. (If the value of C2 (say Rs.240000) is greater than 150000 takes value greater than 150000 but less than 200000 (i.e Rs.50000 maximum admissible) in another cell and if value of C2 is less than (say 140000) or equal to 150000 (say 150000) ignore or takes zero).
        Hope you will assist me in solving problems as the formula is to applied for more than 500 employees.

        • Hello!
          I can help you write the formula if you give an example of the source data, the result, and accurately and briefly describe all the conditions. I don’t have the time or opportunity to try to understand the financial instructions. According to your description, I cannot help you.

          • Thanks. Let be clear the question:-
            Say Value of C2 in Excel is 240000 but maximum limit in C2 is only 150000.
            Value exceeding 150000 in C2 is to be taken in C5 but to the condition that maximum limit in C5 is 50000 and if value in C2 is less than or equal to 150000, the same to be ignore or taken as zero in C5.
            Formula to be apply in C5 uniformly irrespective of amount of value in C2.
            Hope you are clear of my question and assist me by providing the formula.

  2. sir,

    pls help

    IF total of B4=1 then, appear the total of column D7 ( whatever it is.....)

    Thanks,

  3. I'm trying to create a nested IF function that includes an AND condition but get an error that says there are too many arguments. Is there any way to create a nested IF that includes an AND?

    Here are the three conditions/formulas I would like to combine to create a single output in one Status field (the word "blank" is just a placeholder so that I can see the results):

    if F2 is not blank, then return "Done":
    =IF(F2"","Done", "blank")

    if F2 is blank AND E2 is greater than 10 days, then return "2nd call":
    =IF((AND(F2="", TODAY()-E2>10)), "2nd Call", "blank")

    if D2 is blank, then return "1st call":
    =IF(D2="", "1st Call", "blank")

    Here is what I think the full formula should look like:

    =IF(F2"","Done", "blank", =IF((AND(F2="", TODAY()-E2>10)), "2nd Call", "blank", IF(D2="", "1st Call", "blank")))

    Here is a screenshot of sample data:

    https://drive.google.com/file/d/1UtB_MMrfM25MyDX-NFfqw_Zroxopdcj1/view?usp=sharing

    Thank you so much for any assistance you can provide. I have learned so much from your website. I really appreciate the hard work you've put into making these formulas easier to understand.

    • Hello!
      Perhaps this formula will work for you

      =IF(F2<>"","Done", IF((AND(F2="", TODAY()-E2>10)), "2nd Call", IF(D2="", "1st Call", "blank")))

      • Ah, I see what I did wrong by including the "blank" output. That does help. Thank you so much for your assistance.

  4. I need help calculating an Average for multiple subjects in one row
    e.g.
    Student Maths Science Geo

    Peter 80 80% 0 0% 78 78%
    John 50 50% 78 78% 0 0%
    Sue 60 60% 0 0% 80 80%

  5. I am trying to add an SumIF statement to my sum.

    If the Sum of A1:A4, is greater than 100, only display 100.

  6. Hello,

    I hope someone can help me with this.
    If cell C is not blank, result should be "Done"
    If the cell is blank and cell B and cell A result should be the following:
    If 15 Day Difference, "Too early"
    If 30 Day Difference, " Needed"
    If 60 Day, "Cancelled"

    Here is the data:
    Cell C:

    Policy Effective

    02/16/2021
    02/11/2021
    02/11/2021
    01/22/2021

    Cell A & B
    Cell A Cell B
    02/18/2021 02/16/2021
    02/17/2021 02/04/2021
    02/12/2021 02/06/2021
    02/12/2021 02/06/2021
    01/22/2021 01/18/2021
    03/01/2021 02/27/2021

    Thank you so much

    • Hello!
      Please check the formula below, it should work for you:

      =IF(C1<>"","Done",IF(B1-A1<=15,"Too early",IF(B1-A1<=30,"Needed","Cancelled")))

  7. Hi,

    many thanks for the detailed tutorial here. love it!

    i have a1 linked to a drop down list from a separate worksheet.

    i want my m1 to reflect a price based on the item selected from the list in a1 (list of prices also from a table in separate worksheet)

    eg. if a1 is selected as apple, I want this to be reflected in m1. if it is selected as a orange, i want it to be reflected as such in m1.

    how would i do this please?

    many thanks in advance!

  8. Hello,

    I'd like to create a formula for the following, but I don't know how to do it.

    If status in column F is "Draft", then use the File Path in Column I to enter the file name in that folder into Column J called 'File Name'.

    Can this be done with a formula? Or is VB needed?

    Any help is greatly appreciated.

    • Just to give more information on this. The excel worksheet lists information to track a large list of documents. Each document is kept within its own folder. The worksheet lists a lot of data for those documents including the document's status and its File Path.

      Here is the issue: Even though there is only one document in that folder, the file name of the document changes daily. So instead of updating the file names manually (which is tedious and time consuming), I'd like to use the Status column and the File Path column to automatically update the file names of those documents.

  9. Hi there,
    Im trying to return Status (to return Not Started/ Ongoing/ Done/Delayed) of a Task based on starting date, due date and today. I am stuck on adding condition for "Delayed", when Due date has been postponed.

    Today = L2
    Start Date = E3
    Due Date = F3

    I have tried using the OR formula as well, but I cannot add a 4th condition for delayed:
    =if((or(F3>L2="Delayed",E3&F3="",F3<L2)),"Done","Ongoing")

    Could you kindly assist on this question? Thank you in advance!

    • Hi Diana,

      To check multiple conditions and output different results depending on which condition is TRUE, use nested IF functions:
      =IF(F3>L2, "Delayed", IF(AND(E3="",F3=""), "Done", IF(F3<L2,"Ongoing", "")))

  10. How to Calculate if there are 5 parties having different price how to know the which of the party has less price showing the party name

  11. Hello, this seems obvious yet I am stuck and can't find the best way to apply it in excel - pls help:

    I have two different IF functions that are working well each on its own:
    - Function1: =IF(D3="A",'Assumptions Sheet'!$O$10,IF(D3="B",'Assumptions Sheet'!$P$10,IF(D3="C",'Assumptions Sheet'!$Q$10,0)))
    - Function2: =IF(D11="A",'Assumptions Sheet'!$O$9,IF(D11="B",'Assumptions Sheet'!$P$9,IF(D11="C",'Assumptions Sheet'!$Q$9,0)))

    Question: Now, I want to combine them within another IF statement - If CELL A3 = "H" then return the result of Function1 ; If CELL A3 = "L" then return the result of Function2 .

    Thank you.

  12. Help pls. I need a formula for:

    EX: I have a number in cell B1 and if the number is 2, then transfer the number in cell A1 to H1,
    This goes on for 45 numbers. Ex: If number in cell B2 equal 5, then transfer number in cell A2 to H5. etc.

    Can anyone help pls?

  13. I'm having a tough time trying to write a formula where 2 conditions must be true, then using the value from a third formula.

    Sheet -1 (google quiz)
    Score Name Student # Class #
    10 Jim 5 6/1
    6 John 5 6/2
    6 Greg 5 6/3
    9 Tom 5 6/4

    So take the above table for example. That would be the sheet that's linked to a google form and it populates as the students take the quiz. No, I have 4 different classes, 1 sheet for each class. I'm having a hard time writing a formula where say, "condition 1 = 6/2", "Condition 2 = 1 (the student number)", and if both of those conditions are met, then the "score" is automatically populated on the roster sheet.

      • Okay, that helps. However, I'm still having difficulty getting the score as the value.

        So I have multiple sheets that are pulling data from 1 sheet where all my google forms are populating. The google form has a cell "score (1-20)", a cell with the "student numbers (1-40)" and a cell for the "class". However, I have a sheet for each class (6/1 - 6/4), with 40+ students in each. The =AND(logical_expression1, [logical_expression2, ...]) formula highlights those cells, but I can't get it to pull the actual score (from the score cell) when both conditions are met.
        =IF(AND(F1:F100="6/2",E1:E100="1") F1:F150 being the class, E1:E150 being the students number, because each class has students numbered 1-##, there is overlap, therefore I need to have both class and student number to be true, if both of those conditions are met, then I need it to pull the score (1-20) from that specific row.

        • If you refer to multiple cells at once in your formula (F1:F100 rather than F1), you should also wrap it in the ARRAYFORMULA.

          If you're still not sure how to make it work, please consider creating a sample spreadsheet with the following tabs: (1) a couple of sheets with 10-20 lines from your forms (if there's confidential info, replace it with some data but keep the format), (2) an example of the result you want to get.

          Share this spreadsheet with us: support@apps4gs.com, I'll look into it.

          Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

  14. Hello.
    Please I have issue with printing all carry over courses for a particular semester. It always give me the first carryover course and ignore the rest. I used the if nested conditions...
    Please what could be the reason.
    Thank You.

  15. Hi there,

    I have an issue that is along these lines but not precisely what has been discussed thus far...

    I am needing to add values together based on two other conditions being met,
    for example

    If A = 5 and B = 8 add 1 to C
    this was the formula I came up with for the above:

    IF({{a}} = 5 AND {{b}} = 8, VAL('c') + 1, VAL('c'))

    Now my issue is coming in where I need to add multiple criteria to C...
    This formula below does not work but I've used it to explain the thought process

    IF({{a}} = 5 AND {{b}} = 8, VAL('c') + 1, VAL('c')) + IF({{D}} = 5 AND {{E}} = 8, VAL('c') + 1, VAL('c')) + IF({{F}} = 5 AND {{G}} = 8, VAL('c') + 1, VAL('c'))

    Could anyone assist in a correct formula that could work. Based on the above (incorrect) formula I am trying to reach a total of 3 in C.

    Thanks in advance for the brain power.

  16. I'd like to look at 2 cells (J9 and BH9) to see if there is anything in either of them. If something in either cell then show the value in AN9. If nothing is in J9 or BH9, then the IF statement would be skipped.
    I tried several variations of this...
    IF(OR(J9=””,BH9=””),””,AN9)

    • I figured it out after much trial and error.
      IF(AND(OR(J9=""), OR(BH9="")),"",AN9)
      I'm grateful that I found this website.

  17. I want to use the countif statement with two conditions, how do I do this?

  18. Hi,
    Im trying to set a condition where if it says the word "Conns" the answer will be "600" number and if it says the word "MDO" show "570".
    This is one of the formulas I have try so far:
    =IF((D587=Conns,MDO),"600","570")

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

      =IF(D587="Conns",600,IF(D587="MDO",570,""))

      • I was using "" wrong, but thank you for your help.

  19. Hello,
    Is there a way to use an IF or IFS formula if you have more than 127 conditions?

    • Hi!
      You see, there is a limit in Excel that defines how deeply the IF function can be nested. For example, Excel 2007 allows 7 levels of nesting only. Starting from Excel 2010 you may nest up to 64 Ifs. Office 365 increased this limit even more - to 127.

  20. I need help with formula.

    Sheet 1
    Prdtucts Name Buy/sell Qty
    Bed BedBuy 100
    Chair ChairBuy 200
    Table TableBuy 50
    Sofa SofaBuy 300
    Park bench Park benchBuy 440
    Coeffe Tabel Coeffe TabelBuy 602
    Bed BedSell 20
    Chair ChairSell 30
    Table TableSell 12
    Sofa SofaSell 120
    Park bench Park benchSell 40
    Coeffe Tabel Coeffe TabelSell 205
    Table TableSell 30
    Park bench Park benchSell 250
    Chair ChairSell 105
    Tabel TableBuy 45

    I want a formula to find out the stock of each item in different sheet.
    Thanks,
    Sandip Dhakal

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