Exceljet

Quick, clean, and to the point

Excel AND Function

Excel AND function
Summary 

The Excel 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.
Version 
Usage notes 

The AND function is used to check more than one logical condition at the same time, up to 255 conditions. Each logical condition (logical1, logical2, etc.) must return TRUE or FALSE, or be arrays or references that contain logical values.

The AND function will evaluate all values supplied and return TRUE only if all values evaluate to TRUE. If any value evaluates to FALSE, the AND function will return FALSE.

Both the AND function and the OR function will aggregate results to a single value. This means they can't be used in array operations that need to deliver an array of results. For more context, see: Array formulas with AND and OR logic.

Note: Excel will evaluate any number except zero (0) as TRUE.

Examples

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

=AND(A1>0,A1<5)

You can embed the AND function inside the the IF function. 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.

You can combine the AND function with the OR function. The formula below returns TRUE when A1 > 100 and B1 is "complete" or "pending":

=AND(A1>100,OR(B1="complete",B1="pending"))

See more examples of the AND function in formulas below.

Notes

  • The AND function is not case-sensitive.
  • The AND function does not support wildcards.
  • 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.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.