The AND function with example in Excel

The AND function in Excel is used to check if all specified conditions are true. It returns TRUE if all arguments evaluate to TRUE, and FALSE if one or more arguments evaluate to FALSE.

Syntax

scss
AND(logical1, [logical2], ...)

• logical1: The first condition to test that can evaluate to either TRUE or FALSE.
• logical2, ...: Additional conditions to test. You can include up to 255 conditions.

Example

Consider you have a table with student scores and you want to check if students passed both subjects (Math and English). The passing mark for each subject is 50.

Data:

A	B	C
Name	Math	English
John	60	55
Alice	45	70
Bob	80	40
Carol	90	85

Steps to Use the AND Function:

1. Enter the Data:

   • In cells A2 to A5, enter the student names: John, Alice, Bob, Carol.
   • In cells B2 to B5, enter the Math scores: 60, 45, 80, 90.
   • In cells C2 to C5, enter the English scores: 55, 70, 40, 85.

2. Use the AND Function to Check Passing Status:

   • In cell D2, enter the following formula:

     scss
     =AND(B2>=50, C2>=50)
     

   • Drag the fill handle from D2 down to D5 to apply the formula to the other cells in column D.

Results:

A	B	C	D
Name	Math	English	Passed Both
John	60	55	TRUE
Alice	45	70	FALSE
Bob	80	40	FALSE
Carol	90	85	TRUE

Explanation

• =AND(B2>=50, C2>=50) in cell D2 checks if John's Math score (60) is greater than or equal to 50 and if his English score (55) is greater than or equal to 50. Since both conditions are TRUE, the formula returns TRUE.
• =AND(B3>=50, C3>=50) in cell D3 checks if Alice's Math score (45) is greater than or equal to 50 and if her English score (70) is greater than or equal to 50. Since the Math score condition is FALSE, the formula returns FALSE.
• =AND(B4>=50, C4>=50) in cell D4 checks if Bob's Math score (80) is greater than or equal to 50 and if his English score (40) is greater than or equal to 50. Since the English score condition is FALSE, the formula returns FALSE.
• =AND(B5>=50, C5>=50) in cell D5 checks if Carol's Math score (90) is greater than or equal to 50 and if her English score (85) is greater than or equal to 50. Since both conditions are TRUE, the formula returns TRUE.

Practical Use Case

You can combine the AND function with other functions like IF to create more complex conditions. For example, you can display "Pass" or "Fail" based on the result:

• In cell E2, enter:

  arduino
  =IF(AND(B2>=50, C2>=50), "Pass", "Fail")
  

  Drag the fill handle from E2 down to E5 to apply the formula.

Updated Results:

A	B	C	D	E
Name	Math	English	Passed Both	Result
John	60	55	TRUE	Pass
Alice	45	70	FALSE	Fail
Bob	80	40	FALSE	Fail
Carol	90	85	TRUE	Pass

In this example, the IF function combined with AND provides a clear pass/fail result based on the conditions.