AND Function

The AND function is one of Excel's logical functions. It is designed to test multiple conditions simultaneously and return TRUE only if all the conditions are met. If any condition is not met AND will return FALSE. You can use the AND function to check if a series of values all meet certain criteria before performing a calculation or action. The AND function is often combined with other functions like IF, NOT, and OR to construct more complex logical tests. It commonly appears in the logical test of the IF function and in the rules used for conditional formatting and data validation. The AND function is a good way to avoid complicated formulas that involve many nested IFs.

AND function basics

The purpose of the AND function is to evaluate multiple conditions and only return TRUE if all conditions are TRUE. Excel's AND function can handle up to 255 separate conditions, which are entered as arguments with names like "logical1", "logical2", and "logical3", etc. Each "logical" is a condition that must be TRUE. The arguments provided to the AND function can be constants, cell references, or logical expressions. AND will return TRUE only if all results are TRUE:

=AND(TRUE,TRUE,TRUE) // returns TRUE

If any argument is FALSE, AND will immediately return FALSE:

=AND(TRUE,TRUE,FALSE) // returns FALSE

Typically, logical arguments are provided to AND as logical expressions, for example:

=AND(A1>0,A1<5)
=AND(A1>0,B1>0)
=AND(A1="red",B1="small")

All expressions in the formulas above will be evaluated as TRUE or FALSE. Notice that text values used in comparisons must be enclosed in double quotes (""). Be aware that AND will also evaluate numbers as TRUE or FALSE, treating any number except zero (0) as TRUE. You can see this behavior in the formulas below:

=AND(1,2,3) // returns TRUE
=AND(1,2,0) // returns FALSE

Let's look at some practical ways to use the AND function.

Example - number is between

You can use AND to check if a number in a cell is between two numbers. For example, to test if a number is between 10 and 20 we can use AND as seen in the workbook below, where the formula in cell D5 is:

=AND(B5>10,B5<20)

To include the numbers 10 and 20 in the allowed range of values, we can adjust the logic to use greater than or equal to (>=) or less than or equal to (<=) like this:

=AND(B5>=10,B5<=20)

Note: Excel dates are serial numbers so you can use the idea to test if a date is between two dates.

Example - AND with IF

The AND function is often embedded inside the IF function as the logical test to simplify what would otherwise be a more complex formula. For example, in the worksheet below, the goal is to test scores in columns B and C. If both scores are over 750, the result should be "Approve". Otherwise, the result should be "Deny". The formula in cell E5 is:

=IF(AND(B5>750,C5>750),"Approve","Deny")

As the formula is copied down, it returns "Approve" or "Deny" for each row in the data.

IF this AND that

The worksheet below shows a variation of the example above. This time, we want to identify and flag rows where the color is "red" and the size is "small". The formula in cell D5 is:

=IF(AND(B5="red",C5="small"),"x","")

Notice that the AND function is not case-sensitive. The lowercase "red" and "small" work fine even though these values are capitalized in the worksheet.

AND with OR

You can combine the AND function with the OR function to build more advanced conditions. In the worksheet below, the goal is to identify records where the Color is Red or Green and the Quantity is over 100. The logical test inside the IF function is:

AND(OR(B5="red",B5="green"),C5>100)

Like AND, the OR function returns TRUE or FALSE, so AND evaluates the result like any other value.

The full formula in cell E5, copied down, looks like this:

=IF(AND(OR(B5="red",B5="green"),C5>100),"x","")

Notice that text values are enclosed in double quotes ("") but numbers are not.

Example - AND with conditional formatting

The AND function is often used in the rules that trigger conditional formatting. In the worksheet below, we use conditional formatting to highlight rows in the data where the Color is Red or Green and the Quantity is over 100. The conditional formatting is applied to the range B5:C15, and the formula to trigger the rule looks like this:

=AND(OR($B5="red",$B5="green"),$C5>100)

Notice the IF function is not required. Because AND returns TRUE or FALSE, it is a perfect fit for conditional formatting.

Example - AND with a range

It is possible to use AND with a range of values. In the worksheet below, the formula in cell J5 is:

=IF(AND(C5:H5>65),"Pass","Fail")

Note: this is an array formula and must be entered with Control + Shift + Enter in Excel 2019 and earlier. In Excel 2021 or later, the formula will "just work" without special handling.

In the example above, the AND function is used inside the IF function as the logical test. This formula will return "Pass" only if the score for all six subjects is greater than 65. Otherwise, the result is "Fail". See also Must pass 4 out of 6 subjects.

Notes

• The AND function can handle up to 255 conditions in Excel.
• If any condition is FALSE, the AND function immediately returns FALSE.
• 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.