The Excel simple IF function enables us testing a condition and returns True or False value. For example, in a student mark sheet, if Marks is less than 40, its Fail. If marks are greater than or equal to 40, its Pass result. In that case, the IF function may look like this:
=IF(C2>=40,”Pass”,”Fail”)
(Considering C2 contains marks)
In that case, we only checked one cell’s value; it will result True/False.
What if we want to test more expressions and all have to be true for IF function to return True?
For example, we require testing B2 and C2 in the single IF function and on that basis establish the value for D2?
This is where Excel “AND” and “OR” functions can play their role. The AND function determines if all conditions in a test are True.
In the next section, I will show you using the Excel IF in conjunction with AND function for using multiple conditions, so keep reading.
An example of using Excel AND ‘and’ IF functions together
For “AND” and IF function demonstration, consider we have four columns. The B column contains the number of sales while C column contains the Sales Amount.
A salesperson is qualified for a bonus if the number of sales is at least 50 and sale amount is over $10,000. If salesperson is qualified for a bonus, the D columns should display as “Qualified”, otherwise, ‘Not Qualified’.
See how IF/AND functions are used to establish that.
The IF/AND formula for D2:
=IF(AND(B2>=50,C2>10000),”Qualified”,”Not Qualified”)
For D3:
=IF(AND(B3>=50,C3>10000),”Qualified”,”Not Qualified”)
For D4:
=IF(AND(B4>=50,C4>10000),”Qualified”,”Not Qualified”)
And so on. See the resultant sheet with figures:
Let go through row by row to learn how it worked:
- B2 = 35 (False) AND C2 = 8000 (False) so IF function returned False Value i.e. Not Qualified.
- B3 = 55 (True) AND C4 = 10500 (True) – Both conditions are True so IF returned Qualified.
- B4 = 45 (False) AND C4 = 16000 (True) – One condition is False, so IF/AND returned False i.e. Not qualified.
- B5 = 100 (True ) AND C5 = 25000 (TRUE) – Both conditions True (Qualified).
By simple IF function, you could only check one cell; Number of Sales or Sale Amount. The AND enabled testing multiple conditions. However, all conditions have to be true.
Using three conditions in IF / AND functions
You may use even more conditions by using Excel IF in conjunction with AND Function. The following example shows using three conditions.
Let us add another column in the above example, Director Note. It adds “Well Done” text if above two conditions are met along with Bonus column as “Qualified”.
If any of the conditions is False, it should be updated by “Needs improvement” text. The purpose is showing how to use the text in IF/AND function along with adding more conditions.
The formula for E2 cell:
=IF(AND(B2>=50,C2>10000,D2=”Qualified”),”Well Done”,”Needs improvement”)
You see, just separating a condition by co the ma, you may add more conditions.
Using the IF/OR Excel function
The Excel OR function returns True if any of the condition evaluate as True. This is unlike the AND where all conditions have to be True.
An example of Excel IF .. OR
For the example, I am using the same excel sheet as used in the first example with AND function.
The scenario now is, if a salesperson makes 50 or more sales OR the sale amount exceeds $10,000 then he/she is eligible for the bonus.
I am just changing the formula and replacing AND by OR. Same conditions are used so that you may see the difference:
The following formula is used for D2:
=IF(OR(B2>=50,C2>10000),”Qualified”,”Not Qualified”)
The result is False as both conditions are False.
The D3 formula:
=IF(OR(B3>=50,C3>10000),”Qualified”,”Not Qualified”)
Both are True, so Qualified.
The formula for D4:
=IF(OR(B4>=50,C4>10000),”Qualified”,”Not Qualified”)
This is interesting. In the case of IF/AND example, it was False. Now it is showing as “Qualified” so IF/OR returned True. The reason is although number of sales is less i.e. 45 but sale amount exceeded.