The AND function in Excel checks if multiple conditions are met and returns TRUE if only all the conditions are TRUE; otherwise, it returns FALSE.
=AND(logical1, [logical2], …)All conditions are executed simultaneously, and the AND function will return TRUE only if all the specified conditions are TRUE. If even one condition is FALSE, the function also returns as FALSE.
The AND function is used most to evaluate multiple conditions in decision-making scenarios, data validation, and conditional formatting. Although the AND function doesn’t directly include operators, you can use it with logical operators like =, <, >, <=, and >= within the conditions.
A bank evaluates loan applications with conditions such as the applicant’s age should be above 21, and their credit score must be above 700.
=AND(C3>21,D3>700)The formula checks if the applicant’s age is greater than 21 in the first logic, and then their credit score is above 700. For Sarah, the age and credit score both meet our logic and the formula returned as TRUE. For Michael, the age is greater than 21, but the credit score is less than 700 and the result is FALSE.
A company evaluates employees for rewards and recognition where the employee should have an attendance of 90% or above and performance ratings of 4 or above.
=AND(C4>=90%,D4>=4)The formula in this scenario first checks if the Attendance is 90% or above in the first logic, and then if the performance rating is greater than or equal to 4. For Alex, both conditions are met, so the result is TRUE. For Mia and David, you can notice either Attendance is not matching the logic or Performance Rating and the result is FALSE.
Note: When working with percentages, notice I have used the “%” symbol along with the value for accurate results.
A school wants to highlight students whose score is above 80 in both Math and Science.
=AND($C3>80, $D3>80)The formula in this scenario first checks if the Marks in Maths is greater than 80 and then checks in Science. For Arjun, both conditions are met, so the result is TRUE. For Priya and Ethan, you can notice either Maths marks are less than 80 or in Science and the result is FALSE.
A university is providing scholarships if the student’s score is above 85 in Math, and 80 in Science, and their participation in extracurricular activities is marked as “Yes”.
=AND(C3>85,D3>80,E3="Yes")The formula checks if all three conditions are met. Rohan meets all the conditions passed, so the result is TRUE. However, Augustin scored 85 in Math but not greater than the first logic and the result is FALSE.
Steps:
=AND($C3>80, $D3>80)
OR
You can also refer the column E as a helper where we created in Example 2 for more dynamic control where you will change the formula in Conditional Formatting as:
=E3=TRUEResult: Rows meeting the criteria will be highlighted.
An organization wants to promote employees with over 3 years of experience and a rating above 4.5
=IF(AND(C3>3,D3>4.5),"Yes","No")The IF function combines with AND to check if both conditions are met. For Alex, the result is “Yes” instead of TRUE as we have combined AND function with IF. For Mia, it is “No” as the experience criteria have not been met.
A store wants to give discounts to their customers if they spend above $500 or purchase more than 10 items and are members.
=IF(AND(OR(C3>500,D3>10),E3="Yes"),"Eligible","")The OR function evaluates if spending exceeds $500 or if the item purchased exceeds 10, and the AND function ensures the customer is a member. Aman qualifies and the formula returned Eligible and as Anjali does not, it returned blank.
Employees are on probation if they haven’t completed training and their contract is not permanent.
=AND(NOT(C3="Yes"),D3="Temporary")The NOT function checks if the training for employees is completed, and the AND function checks if the contract for employees is Temporary. Alex meets the criteria, so the result is TRUE.
The AND function is an indispensable tool when you want to evaluate multiple conditions in Excel. From eligibility checks to advanced decision-making scenarios, it simplifies logical operations and enhances your data analysis.
No comments yet. Be the first to comment!
Press Ctrl+1 to open Format Cells dialog for any selected range.