For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=FUNCTIONS

AND()

Logical

Returns TRUE only if every argument is true.

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

AND evaluates a list of conditions and returns TRUE only if all of them hold, FALSE if even one doesn't. It's almost always used inside IF, since AND by itself just returns TRUE or FALSE.

Combine with OR to build compound conditions like "both A and B, or C."

The limitation that catches people is that AND reduces whatever it is given to a single TRUE or FALSE. Handed two ranges it asks whether every cell in both satisfies the test, not whether each row does, so =AND(A2:A200="North", B2:B200>1000) answers a question nobody asked. For row-by-row logic, multiply the conditions inside SUMPRODUCT, FILTER or a dynamic-array formula, where TRUE and FALSE coerce to 1 and 0 and the multiplication performs the AND element by element.

Arguments

logical1
The first condition to test.
logical2, ...optional
Additional conditions to test — all must be TRUE.

Examples

=AND(B2>=60, C2="Complete")
→TRUE

TRUE only if the score is 60+ AND status is Complete.

=IF(AND(B2>=60, C2="Complete"), "Pass", "Fail")
→Pass

AND nested inside IF to gate on two conditions at once.

=SUMPRODUCT((A2:A200="North")*(B2:B200>1000))
→27

AND collapses a whole range to a single TRUE/FALSE, so it cannot test row by row. Multiplying conditions is the array equivalent.

Notes and gotchas

  • AND collapses everything to a single TRUE or FALSE. Passing it a range inside an array formula — or inside FILTER — gives one answer for the whole range, not one per row, which silently filters everything or nothing.
  • For per-row AND logic, multiply the conditions instead: (A2:A99="North")*(B2:B99>100).
  • Text values and empty cells in the arguments are ignored rather than treated as FALSE, which can make a test pass when you expected it to fail.
  • Any non-zero number counts as TRUE, including negatives.
  • Beyond about three conditions, a helper column per condition is easier to debug than one long AND — you can see which one failed.

Common errors

What goes wrong with AND, and why
You seeBecauseFix
#VALUE!No argument evaluated to a logical value or a number — typically all arguments were text.Make sure at least one argument is a real comparison or number.
Tips / Tricks / Hacks

AND tricks, tips, and workbook hacks

AND tests whether every condition you hand it is TRUE—if even one check fails, the whole function returns FALSE. You will use it inside the first argument of IF(), inside Conditional Formatting rules to highlight rows meeting multiple criteria, and inside custom Data Validation rules.

One critical limitation in modern Excel: because AND aggregates its entire input into a single scalar TRUE or FALSE, you cannot use AND(ColA="North", ColB=2026) inside array functions like FILTER or XLOOKUP. For row-by-row array logic, multiply the conditions with * instead.

Quick AND checks before trusting the result

  • Use standalone =AND(...) (without wrapping in IF) when writing Conditional Formatting or Data Validation rules—both dialogs already expect a TRUE/FALSE output.
  • To check that a number falls inside a range, write =AND(B2>=MinVal, B2<=MaxVal) since Excel does not support chained 10<=B2<=20 syntax.
  • In FILTER(), SUMPRODUCT(), or array IF(), replace AND(cond1, cond2) with (cond1)*(cond2).
AND tricks and formula patterns
AND patternAND formulaBest useWatch out
Between-two-values range check=AND(C2>=1000,C2<=5000)Testing whether a transaction or date sits inclusively between a lower and upper bound.Excel evaluates 1000<=C2<=5000 as (1000<=C2)<=5000, which is a bug—always use AND().
Highlight entire table row with Conditional Formatting=AND($C2="Overdue",$E2>1000)Shading columns A:F whenever Status (C) is Overdue and Balance (E) exceeds $1,000.Lock the column letters ($C2, $E2) and keep the row number relative (2).
Verify all required form cells are populated=IF(AND(B2<>"",C2<>"",D2<>""),"Ready to Submit","Incomplete")Gating a quote or journal-entry template until every mandatory field has been filled.COUNTA(B2:D2)=3 is a shorter alternative when the required cells are contiguous.
Common AND mistakes and how to fix them
AND mistakeWhat happens in ANDFix
Using AND() inside FILTER() or array formulas=FILTER(A2:D100, AND(B2:B100="East", C2:C100>500)) throws #VALUE! because AND collapses the entire 99-row test into a single FALSE.Multiply the arrays instead: =FILTER(A2:D100, (B2:B100="East")*(C2:C100>500)).
Forgetting $ column locks in Conditional FormattingOnly column A highlights instead of the whole row from A to F.Prefix every tested column with $ ($C2, $E2) inside the AND rule.

AND practice drills

  1. Apply a Conditional Formatting rule =AND($B2="West", $D2>=5000) across a 4-column table A2:D15 so matching rows highlight across all 4 columns.

Related functions