Table of Contents

AND

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.

Basic AND function in Excel

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.

AND function with Text Criteria

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 $200000 and a customer satisfaction score above 90%.

Combining AND with IF

Explanation: in this example the IF-AND formula returns “Not Eligible” because although the sales in cell A2 exceed $200000 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.

Using AND with IF and OR

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.

AND function with 3 Arguments

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 $50000 and a performance rating of 5.

Modify Values with IF and AND

Explanation: in this example the IF-AND formula adjusts the salary because the conditions—salary under $50000 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.

Advanced AND formula

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”.

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog