The Excel AND function tests several conditions at once and returns TRUE only when every one of them is TRUE — one FALSE anywhere and the whole thing is FALSE. On its own it produces TRUE/FALSE flags; inside IF it builds multi-condition rules like “bonus only if units ≥ 100 and rating ≥ 4”. Its permissive twin is OR, which needs just one TRUE.
Syntax
| Argument | Description | |
|---|---|---|
logical1 | Required | The first condition — anything that evaluates to TRUE or FALSE, like B2>=100. |
logical2, … | Optional | Up to 255 conditions in total. AND returns TRUE only if every one is TRUE. |
Available in: every version of Excel, desktop and web. Text and empty cells in the arguments are ignored; if no argument yields a logical value, AND returns #VALUE!.
All conditions must pass
Bonus eligibility needs 100+ units and a rating of at least 4. AND in D2, copied down, flags each rep:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Units | Rating | Eligible? |
| 2 | Avery | 132 | 4.6 | TRUE |
| 3 | Blake | 118 | 3.8 | FALSE |
| 4 | Casey | 87 | 4.9 | FALSE |
| 5 | Drew | 145 | 4.2 | TRUE |
Raw TRUE/FALSE is fine for a flag column, but the everyday pattern wraps it in IF for readable output:
Conditions can reference different cells, sheets, even other functions — a between-two-dates test is classic AND territory:
Try it: interactive AND demo
Toggle the three conditions and watch AND demand perfection — one unchecked box sinks the whole result.
AND vs (a)*(b): why FILTER uses multiplication instead
There’s one place AND will let you down: dynamic-array formulas. AND collapses everything it’s given into a single TRUE/FALSE — it doesn’t return one answer per row. That’s why FILTER conditions are written as multiplication:
Multiplying coerces TRUE/FALSE to 1/0, so (a)*(b) is 1 only when both are TRUE — a per-row AND. The same idea with + gives a per-row OR. Inside a plain IF on a single row, though, AND remains the readable choice.
Counting with multiple conditions? You don’t need AND at all — COUNTIFS and SUMIFS take multiple criteria natively and apply them as an implicit AND.
Errors & common pitfalls
#VALUE! — no logical values found. If every argument is text or empty, AND has nothing to evaluate. Check that your conditions actually compare something: AND(B2, C2) on text cells fails; AND(B2="Yes", C2="Yes") works.
Pitfall: writing 100<=B2<=200. Chained comparisons don’t work in Excel — that expression evaluates left to right and gives a nonsense result rather than an error. Write =AND(B2>=100, B2<=200) for a between test.
Pitfall: using AND inside FILTER or other array formulas. AND aggregates the entire array to one value, so the filter returns everything or nothing. Use (condition1)*(condition2) instead — see the section above.
Pitfall: numbers stored as text. B2>=100 never passes when B2 holds the text “132”. Watch for green corner triangles and left-aligned numbers, and convert with VALUE() if needed.
Practice workbook
Frequently asked questions
How do I use AND inside an IF formula?
=IF(AND(B2>=100, C2>=4), "Bonus", "No bonus"). AND resolves all its conditions to a single TRUE or FALSE, and IF picks the outcome. OR works the same way when any single condition should suffice.How many conditions can AND take?
How do I test if a value is between two numbers?
=AND(B2>=100, B2<=200). Excel does not support the chained form 100<=B2<=200 — it silently evaluates wrong rather than erroring.Why doesn't AND work inside FILTER?
=FILTER(data, (B2:B50>=100)*(C2:C50>=4)). Multiplication acts as a row-by-row AND.What's the difference between AND and OR?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class