MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Handle Multiple Conditions in IF


August 01, 2019 - by Bill Jelen

Excel Handle Multiple Conditions in IF. Photo Credit: Issam Hammoudi at Unsplash.com

When you need to do a conditional calculation, the IF function is the answer. It works like this: If <something is true>, then <this formula>; otherwise <that formula>. In the following figure, a simple IF calculates a bonus for your sales of more than $20,000.

Revenue is in column B. Pay a 2% bonus if revenue is greater than 20000 with =IF(B4>20000,0.02*B4,0)

But what happens when two conditions need to be met? Most people will nest one IF statement inside another, as shown below:


Test for two conditions. One way is a nested function of =IF(B4>20000,IF(C4>0.5,0.02*B4,0),0).

But this nesting gets out of hand if you have many conditions that have to be met. Use the AND function to shorten and simplify the formula. =AND(Test,Test,Test,Test) is True only if all of the logical tests evaluate to True. The following example shows a shorter formula with the same results.

Simplify testing for multiple conditions with AND: =IF(AND(B4>20000,C4>0.5),0.02*B4,0)

If you like AND, you might find a use for OR and NOT. =OR(Test,Test,Test,Test) is True if any one of the logical tests are True. NOT reverses an answer, so =NOT(True) is False, and =NOT(False) is True. If you ever have to do something fancy like a NAND, you can use =NOT(AND(Test,Test,Test,Test)).

Caution

Although Excel 2013 introduced XOR as an Exclusive Or, it does not work the way that accountants would expect. =XOR(True,False,True,True) is True for reasons that are too complicated to explain here. XOR counts whether you have an odd number of True values. Odd. Really odd.

Title Photo: Issam Hammoudi at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.