=IF is used when you want to check for a condition to be either True or False, and based on that, some action needs to be taken. The action could be some text / number to be displayed, or some formula to be executed.
But if you have more than 1 condition to be checked at a time, you can use AND within IF.
AND will check both the conditions together in way that IF
- All conditions are True, the output is True.
- Any even 1 condition is False, the output is False.
Consider the below Sales data.
Objective – Provide incentive of 10% only If Product 2 is sold to Customer A.
The formula will be
=IF (AND (B2 = “Customer A”, C2 = “Product 2”), 10%, 0)
Understanding the formula, how it works
1) 1st Condition is, the Customer should be Customer A.
- B2 = “Customer A”
- This checks whether in cell B2, is “Customer A” present?
- The ” ” double quotes are required as Customer A is text. If we were checking for numbers, the ” ” would not be required.
- Once you copy the formula down, B2 will become B3, B4 and so on. Thus checking for each of the entries.
2) 2nd Condition is, the Product should be Product 2.
- C2 = “Product 2”
- This checks whether in cell C2, is “Product 2” present?
3) AND is used to check both conditions together, thus the 2 conditions and entered inside the AND.
4) Also, AND is used inside =IF, and forms a part of the Logical_test of =IF formula.
5) 10% is the Value if True of =IF.
6) 0 is the Value if False of =IF.
Only when both the conditions are satisfied the incentive of 10% is given, else 0.
Thus, only in the last entry, order 1010, the incentive is 10%. All others have 0%.