🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

Excel AND & OR Functions — Combine Conditions Right (and Why Excel Never Short-Circuits)

|

Excel AND & OR Functions — Combine Conditions Right (and Why Excel Never Short-Circuits)

TL;DR — AND and OR take several TRUE/FALSE tests and return one verdict. AND(…) is TRUE only when every test passes; OR(…) is TRUE when at least one does. They don't decide anything on their own — their job is to feed the first argument of an IF, a conditional-format rule, or a filter. Put them inside the test, not around separate IFs: =IF(AND(B2>=60, C2="Yes"), "Pass", "Fail"). Two things bite: a bare =AND(...) in a cell just prints the word TRUE, and Excel has no short-circuit — it evaluates every argument, so AND(A2<>0, B2/A2>1) can still throw #DIV/0!. Works in all versions.

=IF(AND(B2>=60, C2="Yes"), "Pass", "Fail")   ' both must be true
=IF(OR(B2>=90, C2="VIP"), "Priority", "Normal")   ' either is enough

AND and OR look like the most basic functions in Excel, and in isolation they are. But almost every real bug with them comes from a single misunderstanding: people treat them as if they make a decision, when all they do is count votes and hand back one answer. Get that mental model right and the nesting, the stray TRUE, and the surprise #DIV/0! all stop happening.

What you'll learn

  • The mental model: AND/OR are vote-counters, not decision-makers
  • Why they belong inside IF's first argument, not wrapped around nested IFs
  • Which one means "between" and which means "either" — and why people swap them
  • The bare-formula surprise: a cell that just says TRUE
  • The programmer's trap: Excel doesn't short-circuit, so it can still error
  • The array power — testing a whole column — and where AND/OR can't go

The mental model: a vote-counter, not a decision-maker

Picture AND and OR as tallying a show of hands. Each argument is one voter raising a hand for TRUE or keeping it down for FALSE. AND reports back "unanimous?" — TRUE only if nobody kept a hand down. OR reports "anyone at all?" — TRUE if at least one hand went up.

=AND(B2>=60, C2="Yes", D2<100)   ' TRUE only if all three pass
=OR(B2>=90, C2="VIP")            ' TRUE if either one passes

That's the whole function. It takes a room full of conditions and reduces them to one TRUE/FALSE. It does not choose an outcome, show a label, or take an action — it hands its single verdict to whatever asked. Hold that picture and the next rule becomes obvious: if AND/OR only produce a verdict, something else has to use it.

The #1 rule: they live inside IF's test, not around it

The most common shape people reach for is a tower of nested IFs, when what they actually needed was one AND inside a single IF:

' The nested pyramid — works, but harder to read and to edit
=IF(B2>=60, IF(C2="Yes", "Pass", "Fail"), "Fail")

' AND inside the test — one clean junction
=IF(AND(B2>=60, C2="Yes"), "Pass", "Fail")

Both give the same answer, but the second says exactly what you mean — "both must be true to pass" — in one readable line. This is the division of labour that matters: AND/OR compute the condition; IF computes the result. Whenever you find yourself nesting an IF only because you have "two things that must both be true," you don't need a bigger IF — you need an AND in its first argument. The same combined test drops straight into a conditional-formatting rule or the criteria of a FILTER, no IF required.

"Between" is AND; "either" is OR (the one people swap)

The single most frequent logic error is reaching for the wrong connector on a range test. "Is the value between 10 and 20" is two conditions that must both hold — that's AND, not OR:

=IF(AND(A2>=10, A2<=20), "In range", "Out")   ' RIGHT — between
=IF(OR(A2>=10, A2<=20), "In range", "Out")     ' WRONG — always TRUE

The OR version is always TRUE: every number is either ≥10 or ≤20 (most are both). Reserve OR for "belongs to any of these buckets" — OR(Region="EU", Region="UK") — where the conditions are genuinely alternatives. A quick sanity check: if your two tests point in opposite directions on the same value, you almost certainly want AND.

The bare-formula surprise: a cell that just says TRUE

Type =AND(B2>=60, C2="Yes") into a cell on its own and Excel does exactly what the function promises — it shows you the verdict, the literal word TRUE or FALSE:

=AND(B2>=60, C2="Yes")     ' the cell displays  TRUE  (or FALSE)

That's not a bug; it's the function working as designed. It's a signal you forgot the decision step. A raw TRUE in a results column almost always means "I meant to wrap this in an IF (or a rule) and didn't." The fix isn't to change AND — it's to give its verdict a consumer.

The programmer's trap: Excel doesn't short-circuit

If you come from VBA, Python, or C, you expect AND to stop at the first FALSE — A2<>0 AND B2/A2>1 should never divide when A2 is 0. Excel does not work that way. AND and OR evaluate every argument before combining them, so the division still runs and still errors:

=IF(AND(A2<>0, B2/A2>1), "OK", "No")   ' A2=0 -> still computes B2/0 -> #DIV/0!

There is no AndAlso/&& guard here. To protect a risky calculation you must nest — put the guard in an outer IF so the dangerous part is never reached:

=IF(A2=0, "No", IF(B2/A2>1, "OK", "No"))   ' A2=0 short-circuits before the divide

This is the one case where nesting is correct and AND is wrong. If any argument can error when another is false, AND/OR can't shield you — an outer IF (or an IFERROR wrapper as a last resort) can.

The array power — and the wall AND/OR hit

Both functions accept ranges, and AND(A2:A100>0) tests whether the whole column is positive in one shot — a genuinely useful, underused move for a single "is everything valid?" check that spills in modern Excel.

But this is also where their nature bites. Because AND/OR collapse everything to one verdict, you cannot use them to build a per-row mask. This looks reasonable and is wrong:

=SUMPRODUCT((Region="EU") * AND(Amount>0))   ' WRONG — AND crushes the array to one value

When you need a TRUE/FALSE for each row — the engine behind SUMPRODUCT, SUMIFS, and FILTER — you drop AND/OR and use arithmetic on the boolean arrays instead: * for AND, + for OR.

=SUMPRODUCT((Region="EU") * (Amount>0), Amount)   ' * = row-by-row AND
=FILTER(Data, (Region="EU") + (Region="UK"))      ' + = row-by-row OR

The rule of thumb: AND/OR when you want one answer about a set of conditions; */+ when you want one answer per row. Confusing the two is the root of half the "my array formula returns a single number" questions.

The case-insensitive surprise, same as IF

Comparisons inside AND/OR use Excel's usual =, which ignores case — C2="yes" matches "Yes", "YES", and "yes" alike. Usually helpful; occasionally not. For a case-sensitive test (matching a code like ID vs id) wrap the comparison in EXACT:

=IF(AND(EXACT(C2,"Yes"), B2>=60), "Pass", "Fail")   ' "Yes" only, not "yes"

The judgment call: AND/OR, nested IF, or +/* masks

Three shapes, one decision:

  • AND/OR inside an IF — the default for "N conditions, one outcome." Readable, editable, correct for almost everything.
  • Nested IF — only when a later test can error if an earlier one is false (the short-circuit case), or when the branches ask genuinely different questions.
  • + / * on boolean arrays — when you need a verdict per row to feed SUMPRODUCT, SUMIFS, FILTER, or a spilled result.

The skill isn't memorising the functions — it's recognising, from the shape of the problem, which of these three you're actually holding.

How ExcelMaster helps

Business rules arrive as sentences — flag any EU or UK order over $1,000 that isn't already approved — a mix of ANDs and ORs that's easy to wire up backwards. ExcelMaster turns that sentence into the right structure: an AND/OR inside an IF when you want one flag, or a */+ array mask when the same logic has to drive a SUMIFS or a FILTER. You describe the condition in plain terms; it picks the form that won't silently return "always TRUE" or a stray #DIV/0!.

Frequently asked questions

What is the difference between AND and OR in Excel?

AND returns TRUE only when every condition is true; OR returns TRUE when at least one is. Use AND for "all of these must hold" (including "between X and Y") and OR for "any one of these is enough."

How do I use AND with IF in Excel?

Put AND in IF's first argument: =IF(AND(B2>=60, C2="Yes"), "Pass", "Fail"). AND produces one TRUE/FALSE verdict and IF turns it into your two outcomes. Don't nest a separate IF for each condition.

Why does my AND formula just show TRUE or FALSE?

Because that's all AND returns — a verdict. You forgot to wrap it in something that acts on the verdict. Put it inside an IF (=IF(AND(...), "Yes", "No")) or use it as a conditional-formatting / data-validation rule.

Does Excel's AND function short-circuit like other languages?

No. Excel evaluates every argument, so AND(A2<>0, B2/A2>1) still divides by A2 even when A2 is 0, producing #DIV/0!. To guard a risky calculation, nest it inside an outer IF instead of relying on AND.

How do I combine AND and OR for a per-row condition?

Inside IF you can nest them — AND(x, OR(y, z)). But when you need a result for each row (for SUMPRODUCT, SUMIFS, or FILTER), use arithmetic on the boolean arrays: * for AND and + for OR, e.g. (Region="EU") * (Amount>0).

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-04.

Related guides: Excel IF · Excel IFS & SWITCH · Excel IFERROR · Excel SUMPRODUCT · Excel FILTER