What You Should Know About the AND Function in Excel

There may be times in Excel when you require more than one condition to be true in order to take action or determine an outcome. This is where the AND function proves useful.

AND function in Excel

The AND function in Excel takes a variable number of parameters. Each parameter is a condition you are testing. For example, let’s say you want to test to make sure the values in cell A1 and A2 are both greater than 100, as in the sample spreadsheet below.

Excel AND function

Our formula, using the AND function, would look like this:

=AND(A1>100,A2>100)

Result: TRUE

Now, if you wanted to also test to see if cell A3 > 100, in addition to cells A1 and A2, you would change your AND function to look like this:

=AND(A1>100,A2>100,A3>100)

Result: FALSE

Check out our Excel Lesson Videos to learn how to use more conditional logic functions in Excel.

Every condition must be TRUE

The result of the AND function above is FALSE because the value in cell A3 is 98, which is not greater than 100. The AND function will only return TRUE if every condition within the function’s parameter list returns a TRUE value. If even one condition returns a FALSE value, the entire AND function will return a FALSE value.

Use in conjunction with the IF function

The AND function is particularly useful when used in conjunction with the IF function. Suppose you need to determine who passes or fails a course based on the scores from two exams. In order to pass, a student must have a score higher than 70 on both exams. This is depicted in the sample spreadsheet below:

Excel AND function used in an IF formula

As shown in the sample above, the formula for determining Pass or Fail for the first student (row 2) is as follows:

=IF(AND(A2>70,B2>70),”Pass”,”Fail”)

Result: Pass

The result of this formula is Pass because both exam scores (95 and 82) are greater than 70. The remaining two students would fail because at least one of their exam scores is 70 or lower.

Remember, the AND function in Excel can take a variable number of parameters, each of which must evaluate to TRUE for the AND function to return a TRUE value. One or more FALSE conditions will cause the AND function to return FALSE.

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!