Excel 2024: Handle Multiple Conditions in IF

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

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

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.

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.

Bonus Tip: Use Boolean Logic

I always cover IF in my seminars. And I always ask how people would solve the two-conditions problem. The results are often the same: 70-80% of people use nested IF, and 20-30% use AND. Just one time, in Virginia, a person from Price Waterhouse offered the formula shown below:

---

---

It works. It gives the same answer as the other formulas. Calculate the bonus .02*B4. But then multiply that bonus by logical tests in parentheses. When you force Excel to multiply a number by True or False, the True becomes 1, and the False becomes 0. Any number times 1 is itself. Any number times 0 is 0. Multiplying the bonus by the conditions ensures that only rows that meet both conditions are paid.

It is cool. It works. But it seems confusing when you first see it. My joke in my seminar is, "If you are leaving your job next month and you hate your co-workers, start using this formula".