The AND function in Excel returns TRUE when every condition you hand it evaluates to true, and FALSE the moment one condition fails. It is one of three core logical functions, sitting alongside OR and NOT, and it works as the glue that lets you combine multiple individual tests into a single clean decision your spreadsheet can act on.
Most folks meet AND when they outgrow a single IF statement. A plain IF can check one thing. Pair it with AND, and suddenly you can check three or five or even thirty rules at once. That is huge for invoice approvals, eligibility flags, quality checks, and any spot where two-plus conditions must hold at the same time.
You will see AND show up in conditional formatting rules, in data validation logic, in nested decision trees, and inside array formulas. The syntax is simple. The real skill is knowing when to reach for AND versus OR, and how to combine the two without your formula turning into a maze.
Here is the short version. AND is strict. Every argument must be TRUE. Miss one and the whole thing collapses to FALSE. That strictness is the feature, not the bug. When you need both A and B, AND is your function. When you need either A or B, reach for OR.
This guide walks through the syntax, real-world examples, the most common mistakes, and the patterns pros use to keep AND formulas readable. By the end you will be combining AND with IF, IFS, SUMPRODUCT, and conditional formatting like a pro.
The syntax is straightforward: =AND(logical1, [logical2], ...). You can pass anywhere from 1 to 255 logical tests. Each argument should evaluate to TRUE or FALSE — comparisons, references to cells holding boolean values, or other logical functions all work.
Some quick rules to keep in mind. Empty cells and text strings inside ranges are ignored, but if every argument inside a range is empty or text, AND returns the #VALUE! error. Numbers behave like booleans: zero is FALSE, anything non-zero is TRUE. That last quirk trips up beginners more than anything else.
Type =AND(5>2, 10<20) into a cell. Both tests are true, so the result is TRUE. Now try =AND(5>2, 10>20). The second test fails. Result: FALSE. One bad apple. That is the function in a nutshell.
You can mix data types too. =AND(A1="Yes", B1>100, C1<>"Cancelled") checks a text match, a numeric threshold, and a not-equal-to test all at once. Excel evaluates each piece, then returns a single TRUE or FALSE.
AND accepts ranges too. =AND(A1:A10>0) looks reasonable but actually only checks A1 against zero in older Excel versions. To make it work as expected across the whole range, wrap it in an array context: enter as a Ctrl+Shift+Enter formula in legacy Excel, or rely on dynamic arrays in Excel 365 and 2021. SUMPRODUCT is often a cleaner alternative — more on that later.
If you need every value in B2:B100 to be greater than zero, the modern formula is =AND(B2:B100>0) in Excel 365. In older versions, use =SUMPRODUCT(--(B2:B100>0))=COUNT(B2:B100). Same answer, different mechanics.
AND returns TRUE only when every single argument is TRUE. The moment one argument is FALSE, the whole function returns FALSE. Think of it as a strict, no-compromise gatekeeper for your logical tests. This binary behavior is what makes it perfect for eligibility checks, approval rules, and any decision where partial credit does not count.
AND really earns its keep when paired with IF. Alone, AND just spits out TRUE or FALSE. Wrap it inside IF and you can return whatever you want — a discount, a status, a price, a warning message.
The pattern: =IF(AND(condition1, condition2, ...), value_if_all_true, value_if_any_false). Read it like a sentence. If all these things are true, do this. Otherwise, do that.
Say you award a bonus when a rep hits two targets: revenue above $50,000 AND deals closed above 10. The formula in C2 would be =IF(AND(A2>50000, B2>10), "Bonus", "No Bonus"). Drag it down and you instantly know who qualifies. Miss either target, no bonus. Simple, fair, fast.
Loan applications often need multiple gates. Age between 21 and 65, income above $30,000, no current bankruptcy. The formula: =IF(AND(B2>=21, B2<=65, C2>30000, D2="No"), "Approved", "Denied"). Four conditions, one decision, zero ambiguity.
This is where AND shines compared to nested IF statements. Nesting four IFs deep is painful to read and worse to maintain. AND flattens that logic into a single line.
Nothing says the output has to be text. =IF(AND(A2>100, B2="Premium"), A2*0.15, A2*0.05) applies a 15% discount when both conditions hit, otherwise 5%. The AND test gates which calculation runs. This pattern handles tiered pricing, commission scales, and conditional fees with no fuss.
One thing to watch: order does not matter to AND mathematically — TRUE and TRUE and TRUE returns TRUE no matter how you stack them. But ordering by likelihood of failure can make your formula slightly faster on huge datasets, because Excel may short-circuit. On normal-sized sheets, you will never notice the difference.
Replace nested IF statements with a single readable AND inside one IF wrapper. Three or four conditions on one clean line, instead of four levels of nesting that nobody can untangle six months later.
Highlight rows where several columns meet criteria simultaneously, using mixed references like $B2 to walk down rows. Perfect for status dashboards and overdue invoice trackers.
Test age, income, status, and history in one expression for approval workflows. Loan applications, scholarships, insurance underwriting, and tournament qualifications all run on AND logic.
Restrict cell input to values that satisfy multiple business rules at once. Combine numeric ranges with text constraints in a custom validation formula to keep dirty data out of your sheet.
These two get mixed up constantly. The rule is dead simple. AND means all must be true. OR means at least one must be true. Pick the wrong one and your logic flips inside out.
Quick test. Are you tagging customers who bought BOTH product A and product B? AND. Tagging customers who bought EITHER one? OR. That is the whole distinction.
Real-world rules often need both. Approve if income is above $50k AND age is at least 21, OR credit score is above 750. Translated: =IF(OR(AND(A2>50000, B2>=21), C2>750), "Approved", "Denied"). Two paths to yes. Either one works.
The IF OR function in Excel guide covers the reverse pattern in detail. Once you can fluently mix AND and OR inside IF, you can express almost any business rule on one line.
Sometimes the cleanest formula uses NOT instead of OR. NOT(AND(A, B)) is the same thing as OR(NOT(A), NOT(B)). Sounds academic, but it actually helps when you want not-both logic. =IF(NOT(AND(A2="Cancelled", B2="Cancelled")), "Active", "Cancelled") reads as: if not both are cancelled, mark active.
"I want to find rows where the date is in January OR February." That is OR, even though English speakers might intuit it as "any of these months." If you wrote AND, you would get zero rows — no single date can be both January and February. Read the logic in plain English, then ask whether you mean every or any. That solves 90% of AND/OR confusion right there.
=AND(5>2, 10<20) returns TRUE because both tests pass. Standalone AND is most useful for verifying assumptions in spot-checks or quick scratch cells while you debug a larger formula.
=AND(A1="Yes", B1>100) checks a text match and a numeric threshold together. Two conditions, one clean answer, ready to feed into another formula or a chart filter.
You can also chain comparison operators: =AND(A1>=0, A1<=100) verifies that a value sits inside a percentage range, returning TRUE only when both boundaries hold.
=IF(AND(A2>50000, B2>10), "Bonus", "No Bonus") awards a bonus only when revenue and deal count both exceed targets. The IF wrapper turns the boolean into a meaningful label your team can scan in a status column.
Stack more conditions inside AND for stricter rules without nesting. A four-argument AND is still one readable line — a four-deep nested IF is a maintenance nightmare.
You can return numbers or formulas, not just text: =IF(AND(...), A2*0.15, A2*0.05) applies a discount conditionally.
=AND($B2>100, $C2="Open", $D2<TODAY()) highlights overdue open items above $100. Lock columns with the dollar sign, leave rows relative so the rule walks down the table.
Three columns checked in one rule, applied row by row across thousands of records — no helper column needed, no dynamic array, no array formula gymnastics.
Combine multiple rules to layer colors: red for critical overdue, yellow for approaching due date, green for healthy invoices.
=SUMPRODUCT(--(B2:B100>0))=COUNT(B2:B100) checks whether every value in the range is positive — cleaner than array-entered AND. Works in every Excel version, scales to tens of thousands of rows, and never requires Ctrl+Shift+Enter.
For an even faster modern equivalent in Excel 365: =AND(B2:B100>0) with dynamic arrays does the same job in fewer characters.
The takeaway: pick the technique that matches your Excel version, and prefer SUMPRODUCT when sharing files with mixed-version users.
Mix AND inside OR for two-path approvals: =IF(OR(AND(A2="VIP", B2>0), C2>1000), "Ship Free", "Pay Shipping"). VIPs with any purchase get free shipping, and so does anyone spending over $1,000.
This pattern handles tiered eligibility — multiple paths to yes — without sprawling into a long IFS chain.
Keep the outer wrapper one level deep when you can. Two paths is readable; five paths usually means you need a lookup table instead.
Conditional formatting is one of the most useful homes for AND. Highlight rows that meet several criteria at once, and you turn a wall of numbers into a heat map you can read at a glance.
Open Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format. Then drop in something like =AND($B2>100, $C2="Open", $D2<TODAY()). Lock the column references with the dollar sign so the rule walks down rows without shifting columns. Pick a fill color. Done.
An accounts receivable sheet might have amount, status, and due date columns. The rule =AND($A2>1000, $B2="Unpaid", $C2<TODAY()) highlights only the invoices worth chasing today. Three conditions. One color. Massive time savings.
Want to flag students who failed but had perfect attendance? That signals a tutoring opportunity. =AND($D2<60, $E2=100) highlights exactly those rows. Quick triage, no pivot table needed.
The biggest gotcha here is mixed referencing. If you use $B$2 instead of $B2, the rule locks to row 2 forever and applies the same formatting to every cell. Use $B2 (column locked, row relative) for row-by-row evaluation. This is the single most common mistake people make with AND inside conditional formatting, and once you spot it, you will fix it everywhere on the spot.
AND is mostly bulletproof, but a few sneaky errors show up. Here are the ones you will hit and how to dodge them.
If every argument is a text string with no boolean equivalent — like =AND("hello", "world") — Excel returns #VALUE!. Same story when you point at a range full of text. Fix it by making sure your arguments are real logical tests or numeric values. Numbers convert: zero is FALSE, anything else is TRUE. Text alone confuses it.
In older Excel versions, =AND(A1:A10>0) does not check every cell — it only checks the cell on the same row as the formula. Use array entry (Ctrl+Shift+Enter) or, better, switch to SUMPRODUCT: =SUMPRODUCT(--(A1:A10<=0))=0. That returns TRUE only when zero cells fail the test, which is what you actually wanted.
AND accepts up to 255 arguments. That sounds like a lot, but people writing brute-force rules sometimes blow past it. If you find yourself writing dozens of A=1, B=2, C=3 tests, restructure. A helper column with a single concatenated key and a lookup against a rules table will outperform a monstrous AND nest every time.
An empty cell inside a logical test is treated as FALSE. So =AND(A1>0, B1) where B1 is empty returns FALSE. That is usually fine, but it can surprise you when a blank means not yet filled in rather than no. Use ISBLANK or COUNTBLANK helpers when blank-versus-false matters in your workflow.
Once you have the basics down, a few advanced moves unlock serious productivity.
When you need all cells in this range meet condition X, SUMPRODUCT is faster and cleaner than array AND. =SUMPRODUCT(--(B2:B100>0))=ROWS(B2:B100) returns TRUE only if every value is positive. It scales to thousands of rows without breaking a sweat. This pattern shows up constantly in data validation and dashboard logic.
Technically these functions have AND logic baked in — every criteria pair must match. =SUMIFS(Sales, Region, "North", Quarter, "Q1", Status, "Closed") sums sales only when all three criteria hit. You did not write AND, but you used it. That is why these functions exist: they encode the most common AND-driven aggregations directly.
AND does not natively support wildcards, but you can fake it with helper functions. =AND(ISNUMBER(SEARCH("error", A2)), B2>100) checks whether the word error appears anywhere in A2 AND B2 exceeds 100. SEARCH returns the position number when it finds the substring, ISNUMBER converts that to TRUE, and AND ties it to the second condition.
If your AND-IF tree is checking multiple disjoint cases, IFS may be cleaner. Excel's IFS function evaluates conditions in order and returns the first match. It is not a replacement for AND when you genuinely need conjunction, but it can replace long IF/AND chains where each branch checks a different scenario.
Use IFS for case A, case B, case C. Use AND for every part of one case must be true. The Excel functions reference covers the full logical family in one place.
Here is the cheat-sheet version. Bookmark this section, come back to it any time AND starts behaving weirdly.
Returns: TRUE if every argument is TRUE; FALSE if any argument is FALSE.
Max arguments: 255 individual tests, or as many as fit inside ranges.
Behavior with numbers: 0 means FALSE, everything else means TRUE.
Behavior with text: Treated as #VALUE! unless inside a logical comparison.
Behavior with blanks: Empty cells in ranges are ignored; standalone empty references treated as FALSE.
Use AND any time the answer to which condition matters is "all of them." Bonus calculations, eligibility flags, multi-criteria filtering, conditional formatting on intersecting rules, validation that requires multiple checks — these are all AND territory.
Skip AND when only one of several conditions needs to hit. That is OR. Also skip it when you are aggregating across a range and want a single yes-or-no answer — SUMPRODUCT or COUNTIFS will outperform a clumsy AND-with-array combo every time. And drop AND in favor of IFS if you are switching between many disjoint cases.
Name your boolean helper columns. A column titled IsEligible with a clean AND formula reads ten times better than a giant nested formula buried in the result cell. Decompose your logic into named, testable booleans, then combine them. Your future self — and anyone else who opens the workbook — will thank you.
Another habit that pays dividends: comment every AND formula longer than three arguments. A simple cell note explaining what each test represents saves hours of head-scratching when someone else inherits the workbook. Even better, build a small legend tab listing every named range, every helper column, and every business rule the formulas enforce.
Finally, do not be afraid to break a single big AND formula into two smaller ones. Two clear helper columns, each holding a partial result, then a third column that ANDs them together, is almost always easier to debug than one heroic expression. Spreadsheet readability matters way more than line count, especially when audits or handoffs are on the horizon.
Ready to test what you have learned? The practice quiz linked below covers AND, OR, IF, and common logical-function pitfalls you will see on certification exams.