by 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:
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:
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")
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:
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 :)
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:
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).
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:
To test conditions with the OR logic, use the plus sign:
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")
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")
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:
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))
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".
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", ""))
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:
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", "")))
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:
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)
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)
4538 comments
Hello, i need formula to calculate cost from one sites to another with many option, for example my good from city a to city b cost about $4 per quintal, from city a to c $5 per quintal, from city d to b is $7 per quintal. only 2 destination point but many source point with every point is different cost. thank you
Hello!
You need to find value by two criteria. The answer to your question can be found in this article: How to Vlookup multiple criteria.
I hope it’ll be helpful.
Hi, I'm looking for some help to automate an NBA bracket pool that I am currently running for my office.
I am awarding 1 point for having predicted the correct series score (i.e. 4-0, 4-1, etc.). I have a single cell for each teams score (i.e. Team A's score will go in C5 and Team B's score will go in C9).
I'm hoping there's a formula that can award 1 point for having corrected the correct score. I currently have a "master bracket" where I am entering the series scores as the playoffs continue. I've tried using a formula like =(IF(player1!C5='POOL - MASTER'!E5,1,0)) which will give me a point if correct, but this does not necessarily show whether player 1 guessed the correct series score. So, I need to somehow meet both conditions (or have both scores be correct) in order to award a point.
Any ideas?
Thanks in advance!
LG
*1 point for having PREDICTED the correct score
I failed to mention that these formulas are on a "standings" sheet which I'm hoping will update automatically as I enter the series scores in the "pool - master" sheet.
Hi!
To specify that both conditions must be met, the IF function is used with the AND operator. I recommend reading this guide: IF AND formula in Excel.
Hi
I have a question to solve my case..
I have a table:
G1 to L1 : 1 2 3 1 2 3
G2 to L2 : 4/26 4/26 4/26 4/27 4/27 4/27
With condition :
B3 : 4/26
C3 : 3
D3 : 4/27
E3 : 3
Im trying to make a Grant Chart, i already make it if the condtion just the date. But im not yet solve it with additional condition. Please help :)
I need help with creating a formula in excel
IF A5=5, then 0 points is assigned
IF A5=4, then 1 point is assigned
IF A5=3, then 2 points is assigned
IF A5=2, then 3 points is assigned
IF A5=1, then 4 points id assigned
IF A5=0, then 5 points is assigned
Hi!
You can find the examples and detailed instructions here: Excel nested IF statement - multiple conditions in a single formula.
You can also use the IFS function as described in this article: Use the Excel IFS function instead of nested IF.
I hope my advice will help you solve your task.
Just us Nested - If(AS=5,"0",If(AS=4,"1",If(AS=3,"2",If(AS=2,"3",If(AS=1,"4",If(AS=0,"5"," "))))))
Hey please help me with the the formula for taking R12 from every R100 due .I have tried this but I can't get the correct answer when scrolling down ,=if(d6>=A4,B2,0)
Hi!
To get 12 out of every 100, divide the number by 100 and use the integer part of the number using the INT function:
=INT(A1/100)*12
I have a question,
If the opening size is >0.5= 3 then half opening size deduction
If the opening size is >3 then full opening size deduction
Ex Door = 2.0 x 2.1m = 4.2 Sqm is the opening size, then criteria 2
If Door = 1.2 x 2.1 = 2.52, then half of the opening size (2.52/2) criteria 1
Hi!
I don't quite understand what result you want to get with our conditions. All the necessary information is in the article above. Or explain the problem in more detail.
Is there an excel formula where I want that if the birthdate (Column C) is equal to 12 and is elementary level (Column G) then it will returns to the age (Column E)otherwise returns to "Underage"(Column E).
Hi!
I’m sorry but your description doesn’t give me a complete understanding of your task. Maybe you will find this article useful: How to calculate age in Excel from birthday. If this is not what you wanted, please describe the problem in more detail.
Hi I need help with a nested if argument, I want to calculate a specific percentage according to an alphabetical code, all entries with codes (column F) R,P,B,W need to depreciate by 5% per year (column H) if the code is anything else the percentage is set at 50%
This is what I have so far:
=IF(or(F10="r",f10="p",f10="b",f10="W"),and(f10=n),100%-(H10*5)/100),"50%")
H1!
If I understand your task correctly, try the following formula:
=IF(OR(F10="r",F10="p",F10="b",F10="w"),H10*0.95,H10*0.5)
or
=IF(SUM(--(F10={"r","p","b","w"}))>0,H10*0.95,H10*0.5)
Hi,
I'm trying to work out a formula which deducts travel time from appt time. Column 'P' is actual Appt duration, Column U is overall Appt duration (inc travel) I have the following formula which deducts actual appt time from overall appt time but wanted to include an IF statement saying take column 'U' if it = 00:00 (no travel time) so currently have the following: =IF(U135="00:00",U135,IF(U136<P136,P136-U136,U136-P136))
However this isn't working as still calculates regardless of column 'U' being 00:00, any idea what I'm doing wrong?
Hello,
I am trying to use an "if and" statement with 3 conditions at the end of a very long statement.
It is 3 IF statements, 1 IF AND statement, and 1 IF AND AND statement. The formula works up to the 3 IF statements + 1 IF AND statement, but does not work once I add the IF AND AND statement. I have copied below what I have so far, can someone please assist?
IF(V2="*",S2,IF(S2="",T2,IF(S2="*",S2,IF(AND(S2="",T2="<"),"T2","U2",IF(AND(AND(S2="",T2=""),U2,V2))))))
Above is translated to if V2="*", then look at S2. If S2 is blank, then look at T2. If S2="*", then use S2. If S2 is blank and T2 = "<", then look at T2, if not then look at U2. If S2 is blank, and T2 = "", then look at U2 if not then look at V2.
Hi!
Your conditions are incorrect. If condition IF(AND(S2=””,T2=”<”) returns FALSE, then there are 2 choices: 1) U2. 2) checking the condition IF(AND(AND(S2="",T2="”)
Hi,
I require formula for a confiriton where, "If we input date in one cel, then a specific amount should popup in the destination cell"
Example: if a cell A15 has date 23/04/2022, then cell G15 should popup with the amount 50.
will this be possible?
Hi!
I recommend reading this guide: Using IF function with dates.
It contains answers to your question.
Dear Sir,
I noticed you are helping a lot of people here and i would like to ask if you could guide me in the right direction in excel. I am trying to create a worksheet with available lens (of all sorts) for our lab. And separate worksheets with lenses grouped for uses (contact lenses/ lenses by brands and stuff like that). My idea was for the lens name with its ID from the other worksheets to be copied into the availability worksheet (like this ='Contact Lenses'!D8). But when there is a date of taking the lens in the availability worksheet the worksheet that regards that specific lens (like 'Contact Lenses') will have in the relevant cell a value of Yes (for taken) and a No value for when there is a date of return in a separate column in the 'availability' worksheet. However The problem i am struggling with is that the same lens may occur several times in the same availability worksheet - making If statements kind of ridiculous.
Things like started coming out as such - which obviously won't work
'IF(OR(AND('Availability' $C10:C100 ISTEXT = "CL1.75 #A" , 'Availability' $F10:F100 ISBLANK), AND('Availability' FC10:F100 ISTEXT = "CL1.75 #A", ISTEXT )), "Yes", "No")
('Availability' worksheet) - Cells[Lens ID, date of taking item, name of person who took it, date of returning item]
Example 'Lens' worksheet - Cells [Lens ID, Specification, Available?]
I hope i explained it well enough. Do you have an idea on how to proceed with such a task?
Regards
Wiktor
Hello!
If you need to find the last match with Lens ID in the ‘Availability’ worksheet, you can use the XLOOKUP function with Search_mode = -1
I hope this will help, otherwise please do not hesitate to contact me anytime.
I am trying to get my IF formula to work with multiple conditions. Here are the conditions:
1. Cell C4 must contain a certain term, let's call it "apple".
2. Cell M4 must contain a numerical value.
If both conditions are met, I would like to subtract the value in cell M4 from the value in cell D4.
If one or both of the two conditions are NOT met, I would like to return the value "N/A"
Thanks in advance for your help!
Mike
Hi!
To check if a cell contains a number, use the ISNUMBER function. Also, use the recommendations from the article above.
=IF(AND(C4="apple",ISNUMBER(M4)),D4-M4,"N/A")
This should solve your task.
Thank you Alexander, this worked well. I was not employing the ISNUMBER function, but I had the rest of the formula elements. I really appreciate the swift response.
I have. project and I'm struggling to know how to do this IF formula given what they say:
"The dataset contains ten measurements of student readiness for college in columns B-K. The description of each one of them is available in the file."
"To analyze this data, you need to create additional columns and recode the data, making low a 1, moderate, a 2, and High a 3 (you can use an IF function to do this). In this format, the highest the number, the more prepared the student is."
the column already says moderate low and high what function would I do to get it to formulate to 1,2,3, etc...
Hi!
Please be more specific about your question.
Sorry not all the context was stated in my message, The results , need to be
Poor (345)
Hi!
I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:
=IF(J2 > = 5,"top",IF(J2 > 3,"good","poor"))
If this is not what you wanted, please describe the problem in more detail.
Hi Alexander, I am hoping you can help me, its probably a simple formula, I just can't seem to get my head around.
I have a list of scores ( results) in one column, from these scores I need to determine, poor ( 345). preferably the result being the corresponding text.
Average Total Output
2.3
2.4
2.5
2.6
2.7
2.8
2.8
This is my formula so far: =IF(J2>=5,"top",IF(J23,"good",IF(J2>33>0,"poor",)))) but its not giving me the result I am looking for. Help appreciated.
Hello
Please assist me with this scenario:
Bus fare from A to B - $30 for the elderly
Bus fare from A to B - $50 for adults
Bus fare from A to B - $40 for teenagers
Bus fare from A to B - $30 for kids
Bus fare from B to C - $35 for the elderly
Bus fare from B to C - $55 for adults
Bus fare from B to C - $45 for teenagers
Bus fare from B to C - $35 for kids
I receive trip sheets with passengers' dates of birth and destinations so I want create a formular that says if a passenger is 13 years old, and travelled from B to C, they should have paid $45
Hello!
Pay attention to the following paragraph of the article above - Using multiple IF statements in Excel (nested IF functions). It contains answers to your question.
You didn't provide enough information to write you a formula.
Good afternoon,
I need two different formulas for percentage increase/decrease in one cell.
For more clarification...I am compiling statistics. July 2020 has 0 (crime reported) and July 2021 has 1 (crime reported).
I used this formula: =IF(ISERROR((L23-L4)/D4),0, (L23-L4)/L4) and the percentage is 0%.
I need to show the 100% increase from 2020-2021.
So I used this formula : =if(iserror((L23-L4)/L4),1,(L23-L4)/L4) and the percentage changed to 100%.
Some months have say 5 (reported crimes) in 2020 and 13 (reported crimes) in 2021. For those, the formula =IF(ISERROR((D34-D15)/D15),0, (D34-D15)/D15) works.
Is there a way to combine both formulas for each cell in order to give me an and/or result?
Thank you kindly for your response.
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(ISERROR((L23-L4)/L4),1,IF(ISERROR((L23-L4)/D4),0, (L23-L4)/L4))
I hope it’ll be helpful.
Hello. I need to combine two functions and need help please.
two cells H177 and H65
when H77 is <=0,2 i want to calculate the abs difference and if is 0.05 ok, if not Failed
=IF(AND(H177<=0,2;ABS(H177-H65) 0.2 it responds as Failed,
but how can i combine
when H77 is >0,2 ABS((H177-H65)*100/H77)<20);"OK";"FAILED")
Hi!
I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:
=IF(H177<=0.2,ABS(H177-H65),IF(ABS((H177-H65)*100/H77)<20,"OK","FAILED"))
Hi, I am trying to do the following:
If c2 is blue (true or false) then c3=c2 - 8
If c2 is another colour nothing happens
So.. is it possible to bind c3 to a c2 in that way and still be able to write any number in c3 if condition is not met?
Thanks
Hello!
Standard Excel functions cannot determine the color of a cell. To define the color of a cell, use VBA User Defined Functions as described in this article on our blog.