How to use the Excel AND function

Summary

The AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1<10). The AND function can be used as the logical test inside the IF function to avoid extra nested IFs, and can be combined with the OR function.

Purpose

Test multiple conditions with AND

Return value

TRUE if all arguments evaluate TRUE; FALSE if not

Syntax

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

Arguments

logical1 - The first condition or logical value to evaluate.
logical2 - [optional] The second condition or logical value to evaluate.

Usage notes

Use the AND function to test multiple conditions at the same time, up to 255 conditions. Each logical condition (logical1, logical2, etc.) must evaluate to TRUE or FALSE, or be arrays or references that contain logical values.

For example, to test if the value in A1 is greater than 0 and less than 5, use the following formula:

=AND(A1>0,A1<5)

It can be useful to extend the functionality of functions like IF with AND. Using the above example, you can supply AND as the logical_test for the IF function like so:

=IF(AND(A1>0,A1<5), "Approved", "Denied")

This formula will return "Approved" only if the value in A1 is greater than 0 and less than 5.

Notes:

Text values or empty cells supplied as arguments are ignored.
The AND function will return #VALUE if no logical values are found or created during evaluation.

Related functions

Excel IF Function

The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....

Excel OR Function

The OR function is a logical function to test multiple conditions at the same time. OR returns either TRUE or FALSE. For example, to test A1 for either "x" or "y", use =OR(A1="x",A1="y"). The OR function can be used as the logical test inside the...

Excel Formula Training

Formulas are the key to getting work done in Excel. In this accelerated video course, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn powerful skills to troubleshoot, trace errors, and fix problems. This is the formula training you should have had to begin with. See details here.