Excel AND Function: Syntax, Examples and Tips

Q: How many conditions can the AND function test?

Up to 255 logical arguments in Excel 2007 and later. In practice, more than five or six usually means a helper column or lookup table would be clearer.

Q: What is the difference between AND and OR?

AND returns TRUE only when all conditions are true. OR returns TRUE when at least one condition is true, and FALSE only when every condition fails.

Q: Can AND be used with text?

Yes, as long as each argument is a comparison that returns TRUE or FALSE, such as A2="Excel". Passing bare text like AND("Excel") returns #VALUE!.

Part of the free Module 5: Excel Formulas and Functions · Function 3 of 105 · Full Excel course

The Excel AND function tests two or more conditions and returns TRUE only when every condition is true; if any one fails it returns FALSE. Use AND in Excel inside IF, conditional formatting or data validation whenever a result depends on several requirements being met at the same time, such as a score above 50 and attendance above 75%.

Table of Contents

AND syntax

=AND(logical1, [logical2], ...)

Arguments

Argument	Required	Meaning
logical1	Required	The first condition: a comparison (A2>50), a cell containing TRUE/FALSE, or a function that returns a logical value.
logical2, …	Optional	Up to 254 more conditions. All must be TRUE for AND to return TRUE.

Numbers inside AND are treated as TRUE when non-zero and FALSE when zero. Text values and empty cells are ignored; if every argument is ignored, AND returns #VALUE!.

Step-by-step example

Check whether the number in B1 lies between 10 and 20 inclusive:

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

Excel evaluates B1>=10. With 15 in B1 that is TRUE.
It evaluates B1<=20, also TRUE.
Both are TRUE, so AND returns TRUE. Change B1 to 25 and the second test fails, giving FALSE.

Practical use cases

1. Pass only when two criteria are met

=IF(AND(C2>=50, D2>=75%), "Pass", "Fail")

2. Date falls inside a period

=AND(A2>=DATE(2025,1,1), A2<=DATE(2025,3,31))

3. Highlight rows with conditional formatting

=AND($E2="Open", $F2<TODAY())

Apply to the whole table to flag overdue open items.

4. Cell is filled and above a limit

=AND(NOT(ISBLANK(A2)), B2>=50)

5. Text matches in two columns

=AND(A2="Excel", B2="Advanced")

6. AND without the function

=(A2>50)*(B2="Yes")

Multiplying logical tests gives 1 or 0 and works row-by-row inside array formulas such as SUMPRODUCT, where AND would collapse the whole array to a single result.

Common mistakes and errors

#VALUE! – no argument evaluates to a logical value, for example AND(“Yes”, “No”). Write comparisons instead: AND(A2=”Yes”, B2=”No”).
Using AND in array formulas – AND returns one value for the whole array. Use multiplication of conditions inside SUMPRODUCT or FILTER.
Chained comparisons – 10<=B1<=20 does not work in Excel. Split it into two tests inside AND.
Mixing up AND and OR – AND needs every condition; OR needs any one. “East or West” requires OR.
Text-formatted numbers – “50” is text, so “50”>=50 is FALSE. Convert with VALUE.

Tips and best practices

Put each condition in its own cell during development so you can see which one fails, then combine them in AND.
Use AND in conditional formatting with mixed references ($E2) so the rule evaluates row by row.
Multiply conditions instead of AND inside SUMPRODUCT, FILTER and other array formulas.
Keep date bounds inclusive with >= and <= and build the dates with DATE() to avoid regional parsing issues.
Return 1/0 rather than TRUE/FALSE when the result feeds a SUM (wrap in — or N).

Related functions

OR – TRUE when any condition is met.
NOT – reverses a logical value.
IF – turns the TRUE/FALSE into any result you choose.
IFS – tests several conditions in order without nesting.
Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.

Frequently asked questions

How many conditions can the AND function test?

Up to 255 logical arguments in Excel 2007 and later. In practice, more than five or six usually means a helper column or lookup table would be clearer.