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.
AND formula examples
Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula when you want more control and flexibility.
To allow only a date...
To evaluate several options with a nested IF statement, one approach is to use a separate IF per line to show the result of each set of logical tests. By adding line breaks after each IF function, the formula becomes...
To highlight duplicate values in two or more columns, you can use conditional formatting with on a formula based on the COUNTIF and AND functions.
In the example shown, the formula used to highlight duplicate values is...
To allow only whole number percentages like 5%, 10% and not 5.5%, 10.25%, etc. you can use data validation with a custom formula based on the TRUNC function. In the example shown, the data validation applied to B5:B9 is...
To display a conditional message, without the IF function, you can use boolean logic and the REPT function. In the example shown, the formula in D5 (copied down) is:
=REPT("low",C5<100)
If the value in...
If you want to highlight dates between two dates with conditional formatting, you can use a simple formula that uses the AND and DATE functions together. For example, if you have dates in the range B4:G11, and want to...
To confirm two ranges of the same size contain the same values, you can use a simple array formula based on the AND function. In the example shown, the formula in C9 is:
{=AND(B5:D12=F5:H12)}
Note: this is an array...
To set a check register formula that calculates a running balance, you can use a formula based on simple addition and subtraction.
In the example shown, the formula in G6 is:
=G5-E6+F6
How this formula works
The...
This examples shows how to calculate a volunteer hour requirement. The requirement is to have 15 volunteer hours, with at least 5 in each category. The formula in F6 is:
=AND(COUNTIF(C6:E6,">=5")=3,SUM(C6...
To allow a user to enter only uppercase TEXT, you can use data validation with a custom formula based on the UPPER, EXACT, and AND functions.
In the example shown, the data validation applied to C5:C7 is:
=AND(EXACT...
To highlight the 3 smallest values that meet specific criteria, you can use an array formula based on the AND and SMALL functions.
In the example shown, the formula used for conditional formatting is:
=AND($B3=$G$3...
To evenly distribute a payment or other amount over a given number of months, with a variable start month, you can use a simple formula together with the AND function and a bit of boolean logic. In the example shown,...
If you want to do something specific when two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions with a test, then take one action if the result is TRUE...
To test if a numeric value falls between two numbers, you can use the AND function with two logical tests. In the example shown, the formula in E5 is:
=AND(D5>MIN(B5,C5),D5<MAX(B5,C5))
How this formula works...
To build a Gantt chart, you can use Conditional Formatting with a formula based on the AND function. In the example shown, the formula applied to D5 is:
=AND(D$4>=$B5,D$4<=$C5)
How this formula works
The trick...
Related videos
In this video we show how to highlight approximate match lookups with conditional formatting and the LOOKUP function. Highlighting approximate matches is tricky, because you must replicate the original approximate match in the CF rule.
In this video, we show you how to use conditional formatting to highlight rows and columns associated with exact match lookups. This is a sleek, interactive way to show users matching values.
In this video we look at how to combine the IF function with the OR and AND functions to test more than one condition at the same time.
Related functions
Good links
