AND Function

The AND function of Excel is used to compare more than one logical condition. Up to 255 conditions can be compared at a time, which is provided as arguments. Each argument must return True or False or a value that can be treated as True or False. The purpose of AND function can be understood for the example: if A1 contains the number 60, then:

=AND(A1>0,A1>10,A1<100) // returns TRUE
=AND(A1>0,A1>10,A1<30) // returns FALSE

The AND evaluates all arguments and returns True only if the values of all arguments are evaluated to TRUE. If any value is evaluated as False, then the AND function will return False.

Excel evaluates any number except zero (0) as True.

Both AND function and OR function will accumulate the results as a single value. Therefore, they cannot be used in array operations which need output as an array of results. There are some workarounds to this limitation.

Example

The AND function can be used like this to check if the value in A1 is greater than 0 and less than 10:

=AND(A1>0,A1<10)

AND function is mostly used as embedded in the IF function. We can use AND function as a logical_test for IF function in the example above like this:

=IF(AND(A1>0,A1<10), "Accepted", "Rejected")

This formula will return “Accepted” only if the value in A1 is greater than 0 and less than 10. We can also combine the AND function with the OR function like below:

=AND(A1>80,OR(B1="Under Review",B1="In Process"))

The formula will return True when A1>80 and B1 is “Under Review” or “In Process”:

Notes

• Wildcards cannot be used with the AND function.
• It is not case-sensitive.
• Text or empty cells is argument is ignored.
• The AND function returns #VALUE if no logical arguments are found.