How to use nested IF statements in Excel with AND, OR, NOT

Corporate finance, Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

Following up on last week’s , here’s one on how to use Excel’s IF statement when you have multiple conditions to meet.

The original question I received from a reader, which prompted this series, was about how to use the IF statement in Excel when you had two conditions that had to be met. For example, when sales fell between a minimum and maximum number. Let’s take a look at our example again. Note that I’ve added the maximum amount of $200K into cell B3:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

Bonus 12%
Minimum amount 100000
Maximum amount 200000
Sales

Bonus
Salesman A 87925
Salesman B 100000
Salesman C 145000
Salesman D 200750
Salesman E 178650
Salesman F 99555
Salesman G 147000
Salesman H 213450
Salesman I 122680
Salesman J 92500

Ok, now back to the tutorial.

Now, let’s suppose sales have to be greater than or equal to $100K and less than $200K for a salesman to receive a 12% commission rather than just be greater than $100K, as in our introductory example. How would you write that in “Excel-speak”?

It turns out that you can use , which Excel calls a logical operator (just like it calls the IF function). And, as usual, unlike how most other programming languages work, the syntax required in Excel is a bit different. To use it correctly, you have to write it like the following:

=AND(first condition, second condition, …, etc.)

(In other programming languages, AND would fall in between each condition, just like how we normally talk, but not in Excel!)

Let’s go back to the concrete example. To write the condition that sales have to fall betwen $100K and $200K for the salesman to receive a 12% commission, we’d write the following in cell C6:

=IF(AND(B6>=$B$2, B6<$B$3),B6*$B$1,"No bonus")

Like this:

Translated into plain English, our IF statement now reads, “If B6 is greater than or equal to B2 and B6 is less than B3, then multiply B6 by B1. If not, then put ‘No bonus’ into the cell.” In the first case, our salesman didn’t meet the $100K requirement, so the AND function returned a false, so the IF statement put “No bonus” into the cell. By the way, in our case, we only had two conditions to meet, but if we had more, we could just keep adding them into the list of conditions in the parenthese after the AND function.

Here’s how the results look if we do this for all the salesmen in our example:

There are two other logical functions, and , which work the exact same way:

=OR(first condition, second condition, …, etc.)
=NOT(first condition, second condition, …, etc.)

For example, we could actually rewrite our IF statement above using the OR condition if we’re careful. Here’s how it would look:

=IF(OR(B6<$B$2,B6>=$B$3),”No bonus”, B6*$B$1)

Like this:

Again, in plain English, what we’re saying is, “If B6 is less than B2 or B6 is greater than or equal to B3, put ‘No bonus’ into the cell. Otherwise, multiply B6 by B1.” We’re basically testing for the opposite conditions in our OR statement as we did in our AND statement. And you’ll get the same results as above with the AND statement, as seen below:

You might be able to picture now how you could start using multiple AND, OR, and IF statements nested altogether to test all sorts of complicated conditions in Excel. But keep in mind that cells in Excel are really meant more for doing calculations than programming, which is really what you’re getting into once you start putting in complex IF statements. (I’ve read somewhere you can nest up to 7 IF statements in Excel, though I’m not sure to which version(s) of Excel that limitation applies.)

In particular, coming up with a correct list of conditions to test for, in the right order, that don’t result in a false positive test (e.g. a condition accidentally being met that you didn’t mean to) is a big concern in Excel. The other problem is usability. Even if you were able to come up with a complex IF statement that did what you wanted it to, imagine you put the spreadsheet away and revisited it again in a year’s time when you had to update annual sales figures and calculate bonuses again. Here’s an example of a complicated nested IF statement with no other logical operators:

=IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4)))

Think about it: would you really want to spend an extra hour or two decoding that and figure out what you needed to change or update a year later? Similarly, if you had to use a worksheet that someone else wrote that contained a bunch of complicated IF statements, wouldn’t you go nuts? I would!

The statement above is actually even in “reverse” order of what you might normally expect. (In case you’re wondering, I pulled this example from the bottom of a webpage here. It provides the original reader’s question, though why he or she wanted to calculate that, I’m not sure!)

I’ll follow up this post with one on conditional formatting, and I’ve had a request to explain VLOOKUP, HLOOKUP, and pivot tables. Feel free to suggest other tutorials you’d like to see, and I’ll try my best to get to them!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

890 Feedbacks on "How to use nested IF statements in Excel with AND, OR, NOT"

AP

Hello!
I am trying to right a nested IF statement and no luck. Each of the below statements works individually, but when I try using them together I am getting the #Value error. Any help would be greatly appreciated!

=IF(AND(R4=Q4,T4=S4), “Match”, “No Match”),IF(AND(T4=S4,R4Q4),”Campaign No Match”,””),IF(AND(R4=Q4,T4S4),”Adgroup No Match”,””)

Context:
If values columns R and Q match as well as values in columns T and S, then the value should show “Match”

If values in columns T and S match, but values in columns R and Q do NOT match then the value should show “Campaign No Match”

If values in columns R and Q do match, but values in T and S do NOT match then the value should show “Adgroup No match”

thanks in advance



ASLINAH

A B C
12345 10
12345 20
12345 LTP 30
12346 25
12346 30
12346 LTP 24

how to make formula if column A have same value, take column B “LTP” . The answer should return for 12345 ,30 & 12346 , 24



Kane

To AP:

=IF(AND(Q4=R4,S4=T4),”Match”,IF(AND(S4=T4,NOT(Q4=R4)),”Campaign No Match”,IF(AND(Q4=R4,NOT(S4=T4)),”Adgroup No match”,””)))

Being ‘nested’ each subsequent logical needs to be contained within the [value_if_false] parameter of the if statements – the ,”” at the end says that if none of the statements are true, return nothing.

Hope it helps!



ravi

Hello all,

Is below formula correct?

=IF(L4= “1650”, “XM3”, “XM2”, IF(L4= “1602”, “XM3”, “XM2”, IF(L4= “1674”, “XM2”, “XM3”, IF(L4= “Alpha Model DM3X”, “XM2”, “XM3”))))

I am getting an error, so many arguments.

Kindly help.



Laxman Poyrekar

X Column Male Y Column >=1.5% “Yes”,”No”, X Column Female Y Column >=2% “Yes”,”No”

Please send formula



Laxman Poyrekar

help on urgent basis



Catero

I am trying to do 3 (IF(OR in 1 statement but keep getting #VALUE or FALSE returned. I’m tearing my hair out – I hope someone can help me

This returns FALSE:

=IF(OR(B2=”Kitchen and Bathroom”,B2=”Bathroom and WC Only”),”KIT014″,”NO DATA”) =IF(OR(B2=”Bathroom Only”,B2=”Kitchen and WC Only”),”BAT009″,”NO DATA”) =IF(OR(B2=”WC Only”,B2=”Kitchen Only”),”KIT031″,”NO DATA”)

And this returns #VALUE:

=IF(OR(B5=”Kitchen and Bathroom”,B5=”Bathroom and WC Only”),”KIT014″,”NO DATA”), IF(OR(B5=”Bathroom Only”,B5=”Kitchen and WC Only”),”BAT009″,”NO DATA”), IF(OR(B5=”WC Only”,B5=”Kitchen Only”),”KIT032″,”NO DATA”)



Marie

Hi, It seems you are good at ecxel.

I need your help and I really hope you can help med.

I need to make a IF(AND( function but I have 13 intervals which exceed the limit for nesting.

This is the criteria
5<=A1<=9 then its 3000
10<=A1<=19 then its 5000 and so on.

How do I do this as a function when I cant use IF(AND because it only allow 7 intervals.

Please help med!

Best Regards

Marie



Rob

I have the same problem as Marie. Any assistance would help?



DZane

How many arguments can you have in an =if(and( function? I have 2 and it works fine. Once I add a 3rd, it says I have too many arguments for this function. The function that works looks like this: IF(B9B3,B9B4,B9<A6),(B3*C3)+((B4-B3)*C4))+((B9-B4)*C5)))

The function that doesn't work looks like this: =IF(B9B3,B9B4,B9B5,B9<b6),(B3*C3)+((B4-B3)*C4))+((B5-B4)*C5))+((B9-B5)*C6))))))

Please help!



DZane

Please disregard my formula examples. They did not copy and paste correctly.



ABIR SHARMA

if cell c3-d3>300, then c3-d3, if c3-d3=c3 then d3, which function is apt to express this logic



swapnil karnekar

logical & text function information



Dwayne Kilbourne

It appears that you technically have a mistake if you have a maximum of $200k to receive the bonus… when using the OR option instead, you list the following formula:

=IF(OR(B6=$B$3),”No bonus”, B6*$B$1)

Shouldn’t it be =IF(OR(B6$B$3),”No bonus”, B6*$B$1)

B6 should just be greater than $B$3 (if it is equal to it, somebody selling exactly $200k will show not getting a bonus when, in fact, he or she should since that is the maximum.



najib

plaesa send me pdf file



Kayla

Your ads keep commandeering the page so that it won’t let me scroll to anything else. Good information on this page but I can’t access it.



Christopher Nahas

How do we Combine these three Formulas:
=IF(AND(C19=41),”1″,”0″)
=IF(AND(C19=81),”2″,”0″)
=IF(C19>=100,”3″,”0″)



Abid Anwar

i am a dealer..

i sell cars on installments
i need a formula in excel which automatically due the amount after due date.

3 years installment
first installment: 49000
2nd installment: 50000
3rd installment: 50000
every month installment: 8000
33 months installment plan
half year installment: 45000
last installment 66000



JBush

Christopher Nahas:

=IF(C19=41,”1″,IF(C19=81,”2″,IF(C19>=100,”3″,”0″)))



Szu

Hi!
Can you help to see what is wrong with my formula? Thanks

Try to see if column F= H, V, ST, OT ,DT then sum up the number on Column H7 to H33.

=IF(OR(F=”H”,F=”V”,F=”ST”,F=”OT”,F=”DT”),SUM(H7:H33),””)



Melissa Intermediate excel knowledge

I can’t figure out what I’m doing wrong with my formula. I’m trying to show that if cell c is less than one date and has a volume over $15,000 in cell F than it’s true, if it’s false than cell c is is over one date and has a volume over $75,000. Please help, been struggling with this for 2 days. =IF(AND(C1515000),”1″,IF(AND(C15>(TODAY()-120),F15>75000),”2″,””))



Joseph T

The following formula returns a value 0.
=IF(N15>P$2,0,IF(N15<Q$2,0,N15))

Cell N15= -2,153

Cell P2= 200
Cell Q2-=-200

I would expect an answer of -2,153. Can you please tell my where I am wrong. Thank you



Wilfred

What is wrong with the below formula?

=IF(AND(A2=”MANAGER”,B2>=5000),0.3*B2,0),IF(AND(A2=”ACCOUNTANT”,B2>=500),0.2*B2,0)

That formula aims to allocate a tax of 30% of B2 to an employee who is a Manager earning 5000 or over, and a tax of 20% of B2 to an employee who is a accountant earning 5000 or over.

The formula returns #VALUE!

Please put right this formula if you can
Thanks



Wilfred

Correction, its 5000 for the accountant case as well not 500



RABIAH

An agent is either on Team 1 or Team 2
If a team reaches their target, the amount of money is divided amongst their team members. Each member receives the same amount.

What am I doing wrong?
=IF(C44=””,IF(OR($A44=1,C32),($A44=2),C39)),IF(C44″”,”0″)

C44 = if they callout sick, they do not receive the payment. I place some text in this cell.
$A44 determines which team they are on (1 or 2)
C32 holds the payout amount of Team 1
C39 holds the payout amount for Team2
but these payouts are given as longs as C44 is blank. If there are no payouts for the month, the amount should be blank.

What am I doing wrong?



Ab

Dear Wilfred

There is one thing that went wrong. In the first if condition the else “0” shouldn’t be there rather the “else” is the next if condition. Here is the correct formula:

=IF(AND(A2=”MANAGER”,B2>=5000),0.3*B2,IF(AND(A2=”ACCOUNTANT”,B2>=5000),0.2*B2,0))



Ab

Dear Rabiah,

please try this :

=IF(AND(C44=””,A44=1),C32,IF(AND(C44=””,A44=2),C39,””))



Rabiah

Dear Rabiah,

please try this :

=IF(AND(C44=””,A44=1),C32,IF(AND(C44=””,A44=2),C39,””))

This did not work. It’s giving me a #NAME? error

I was able to get it to work with this:
=IF(AND(C44=””,C32″”,C39″”),IF($A44=1,C32,IF($A44=2,C39)))+IF(C44″”,”0″)

but only if there are no amounts are in C32 OR C39. This tells me that only the IF(C44″”,”0″) portion is working.
Once I apply the same formula to those months that have a value in either C32 or C39, it gives me #NAME? error.



rabiah

for some reason the is not showing when I send you my example.

=IF(AND(C44=””,C32″”,C39 ″”),IF($A44=1,C32,IF($A44=2,C39)))+IF(C44 ″”,”0″)



rabiah

not equal sign



Rabiah

Good news:
I was able to get your solution to work only by doing the following:

=IF(AND($A44=1,C44=””),C32,IF(AND($A44=2,C44=””),C39,””))

For some strange reason, it worked when I switched the values around. But it opened up another issue, What if the agents change teams at some point of the year? Once I adjust the team number, all of the subsequent months (formulas,values) will change as well. UGH!!



Kulbir Singh

Hi,

I am using the formula for nested if
=IF(AND(H2TIME(4,0,1),H2TIME(24,0,0)), H2<TIME(48,0,0)),"Less Than 2 Days","More than 2 Days")))

However after execution it only take Less Than 4 Hours", & More than 2 Days this parameters and not considering the AND logicals



Kulbir Singh

=IF(AND(H2TIME(4,0,1),H2TIME(24,0,0)), H2<TIME(48,0,0)),"Less Than 2 Days","More than 2 Days")))



διαιτα ατκινς

Good information. Lucky me I recently found your website by accident (stumbleupon).
I’ve bookmarked it for later!



Meme John

Really helpful. Good work!



Vijay Jangam

=IF(AND($B2=”Flat Rate”,$D4=”Yes”),($X2+$Q2),$X2), IF(AND($B2=”Area Rate”,$D4=”Yes”),($V2+$Q2),$V2)

This returns #VALUE. Can you please help?



Pedro Goncalves

When you Nest IF functions, with any other functions, you cant put it in the form of [=IF(A1=B1,C1,D1)]., where C1 is the “if true”, and teh D1 is teh “if false”.

You need to put it without the “false” argument. An example is as follows;
[ =IF(A1=B1,C1,IF(A2=B2,C2)) ]
The reason for this, is that you need to give excel the possibility of only having 1 true value at a time. When you put a “if false” value at the end of multiple IF functions, you allow excel to have more than 1 correct answer. Hope this helps.



Shawn C

I would like to create an excel function using an Or statement with vlookups in an if statement across 3 different worksheets.

Context: I want to determine if items in my master worksheet exist in any 1 of the 3 worksheets. It seems the vlookups in the IF statement will only function as AND function and not an OR function.



Diana

I am sharing vacation costs with a friend. I use Visa, MC, AE1 and 2906 credit cards. He uses different credit cards. I can make the formula work great with just Visa, but when I try to add in the other cards as options (OR statements), I can’t get it to work. Here is the correct statement with just the Visa card:
=IF(AND(D148=”Visa”,L148=”X”), (H148+I148)/2,0)

Here is the statement I can’t get to work:

=IF(AND(D148=(OR(“Visa”,”MC”,”AE1″,”2906″)), L148=”X”), (H148+I148)/2,0)

Oh my gosh. I figured it out!

=IF(AND(OR(D148=”Visa”,D148=”MC”,D148=”AE1″,D148=”2906″),L148=”X”),(H148+I148)/2,0)

Your tutorial really helped me think this through.



Diana

One slight correction to the above comment. I had to change the name of “2906” to “OLDV” because it would not work with a number (at least I didn’t want to bother dealing with figuring out how to make it work….)



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.