AND function in Excel
The AND function in Excel evaluates multiple conditions and returns TRUE only if all conditions are TRUE.
This guide provides a comprehensive understanding of the AND function, starting with basic usage and progressing to more advanced formulas.
Basic AND function
Let's start simple. For example, consider the AND function shown below.
Explanation: in this example, the AND function returns TRUE because the price in cell A2 is greater than $50 and the rating in cell B2 is 4.5 stars, meeting both conditions.
AND function with Text Criteria
Determine if a project is complete and has received positive feedback.
Explanation: in this example, the AND function returns TRUE because the project status in cell A2 is "Complete" and the feedback in cell B2 is "Positive", meeting both criteria.
Don't forget, always enclose text criteria in double quotation marks when using Excel.
Combining AND with IF
Determine if an employee qualifies for a bonus based on sales exceeding $200,000 and a customer satisfaction score above 90%.
Explanation: in this example, the IF-AND formula returns "Not Eligible" because, although the sales in cell A2 exceed $200,000, the satisfaction score in cell B2 is only 89%, failing to meet the second condition.
Tip: visit our page about the IF function to learn more about this Excel function.
Using AND with IF and OR
Determine if a customer is creditworthy based on a credit score over 700 and an active or pending account status.
Explanation: in this example, the IF-AND-OR formula returns "Creditworthy" because the credit score in cell A2 is over 700 and the account status in cell B2 is "Active", meeting the criteria of the OR function (either active or pending).
Tip: visit our page about the OR function to learn more about this Excel function.
AND function with 3 Arguments
Determine if an employee's conference request should be approved based on senior staff status, budget compliance, and advance notice.
Explanation: this example shows how the AND function evaluates more than 2 criteria. Although the employee is designated as senior staff and the advance notice of 70 days meets the criteria, the request is not within budget, resulting in a "Not Approved" decision.
Fun fact: the AND function in Excel can handle up to 255 individual conditions!
Salary Increase
Determine if an employee should receive a 10% salary increase based on a salary under $50,000 and a performance rating of 5.
Explanation: in this example, the IF-AND formula adjusts the salary because the conditions—salary under $50,000 and a top performance rating—are both met, qualifying the employee for the raise.
Advanced AND formula
Determine if all sales values in the range A2:A5 exceed 200 units and all cost values in the range B2:B5 are less than $50.
The formula uses the MIN function to check if the minimum value in the sales range (A2:A5) is greater than 200. If it is, this confirms that all sales values are above 200. Similarly, the MAX function checks if the maximum value in the costs range (B2:B5) is less than $50. If this condition holds true, all cost values are below $50.
The IF-AND formula returns "Yes" if both conditions of the AND function are met. Since the cost in row 3 exceeds $50, the result is "No".
If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Excel Functions and Data Analysis in Excel.
