AND()
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 only if the score is 60+ AND status is Complete.
=IF(AND(B2>=60, C2="Complete"), "Pass", "Fail")AND nested inside IF to gate on two conditions at once.
=SUMPRODUCT((A2:A200="North")*(B2:B200>1000))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
| You see | Because | Fix |
|---|---|---|
| #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. |
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 pattern | AND formula | Best use | Watch 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. |
| AND mistake | What happens in AND | Fix |
|---|---|---|
| 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 Formatting | Only 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
- 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.



