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 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.