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

Following up on last week’s introductory post on using the IF function, 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 Excel’s AND function, 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, OR and NOT, 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**

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

## 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!!

## Please Leave a Comment!