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.
Usage notes
The AND function is used to apply more than one 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.
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:
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":
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.
AND formula examples
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...
To check if a date is within the last n days of today's date, you can use a formula based on the TODAY and AND functions. In the example shown, we are checking for dates in the last 7 days. The formula in D5, copied...
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...
To highlight intersecting row(s) and column(s) with conditional formatting based on exact matching, you can use a simple formula based on mixed references and the OR function.
In the example shown, the formula used to...
To check if a date is within the last n weeks of today's date, you can use a formula based on the AND, TODAY, and WEEKDAY functions. In the example shown, we are testing for dates in the last 2 weeks. The formula in...
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 return "Pass" when any 4 subjects have a passing score, and "Fail" when not, you can use a formula based on the IF and COUNTIF functions. In the example shown, the formula in I5 is:
=IF(COUNTIF(C5:H5,">=70...
If you want to use conditional formatting to highlight cells that are NOT between two values (a lower and upper limit), you can use a simple formula that returns TRUE when a value meets that condition. For example, if...
To test a cell to see if contains certain words but not others, you can use an array formula based on the COUNT and SEARCH functions, wrapped in the AND function.
In the example shown, the formula in C5 is:
{=AND(...
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 allow only unique numbers in a given range, you can use data validation with a custom formula based on the AND, ISNUMBER, and COUNTIF functions.
In the example shown, the data validation applied to B5:B9 is:
=AND(...
To highlight values in one column when values in one or more other columns are blank, you can use the OR function and a basic logical expression to test for blank or empty values. In the example shown, conditional...
Note: Excel contains many built-in "presets" for highlighting values above / below / between / equal to certain values, but if you want more flexibility you can apply conditional formatting using your own formula as...
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...
Related videos
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.
In this video, we'll look at how to highlight the results of approximate match lookups with conditional formatting.
In this video, we'll look at how to use conditional formatting to highlight entire rows using multiple criteria.
In this video, we show you how to create a conditional formatting rule that uses a formula to highlight cells that meet specific conditions not included in Excel's presets.
In this video, we'll look at how to extend a conditional formatting formula so that so that it checks more than just one condition.
Related functions
Good links
