Boolean Formula in Excel: AND, OR, NOT with Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

A boolean formula in Excel is any formula that evaluates to TRUE or FALSE. You build one by comparing values with operators such as = or >=, then combining those comparisons with the logical functions AND, OR and NOT [1]. The result is a Boolean value you can feed straight into IF, FILTER or conditional formatting.

Quick Answer

A boolean formula returns exactly one of two values: TRUE or FALSE [1].
AND returns TRUE only when every condition is true. OR returns TRUE when at least one is true. NOT flips a value to its opposite.
Comparisons like B2>=60 and C2="yes" are themselves boolean expressions, so they plug directly into the logical functions.
Wrap the result in IF to turn TRUE and FALSE into readable labels such as Pass and Fail.
In array contexts, Excel treats TRUE as 1 and FALSE as 0, which is why multiplication can stand in for AND inside FILTER [2].

Syntax

Each function takes logical arguments, which can be comparisons, cell references or other boolean formulas.

Function	Argument	Required?	Meaning
AND	logical1	Yes	First condition to test
AND	logical2, ...	No	Additional conditions, up to 255 total
OR	logical1	Yes	First condition to test
OR	logical2, ...	No	Additional conditions, up to 255 total
NOT	logical	Yes	The value or expression to reverse

The general forms are:

$$ \text{AND}(c_1, c_2, \dots) \quad \text{OR}(c_1, c_2, \dots) \quad \text{NOT}(c) $$

AND returns TRUE only if all conditions are TRUE. OR returns TRUE if any condition is TRUE. NOT returns TRUE for a FALSE input and FALSE for a TRUE input.

How It Works

Excel evaluates each comparison first. In B2>=60, Excel checks the number in B2 and produces TRUE or FALSE. In C2="yes", it compares the text and produces TRUE or FALSE. Those two Boolean values then become the inputs to AND.

The logic follows standard Boolean algebra, the same system behind propositional formulas in logic [1]. Truth tables make the behavior easy to predict.

Condition 1	Condition 2	AND	OR
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
FALSE	TRUE	FALSE	TRUE
FALSE	FALSE	FALSE	FALSE

NOT works on a single value. NOT(TRUE) is FALSE and NOT(FALSE) is TRUE. You can nest functions, so NOT(AND(B2>=60,C2="yes")) returns TRUE for anyone who fails either test.

Text comparisons are not case sensitive in Excel, so C2="yes" matches "Yes", "YES" and "yes". Numbers compare by value, and dates compare as serial numbers, so A2>TODAY() is a valid boolean test.

Worked Example

This sheet tracks six students with a score and an attendance flag. Column D holds the boolean formula and column E converts it to a status.

	A	B	C	D	E
1	Student	Score	Attendance	Eligible	Status
2	Ana	78	yes	`=AND(B2>=60,C2="yes")` -> displays TRUE	`=IF(D2,"Pass","Fail")` -> displays Pass
3	Ben	55	yes	`=AND(B3>=60,C3="yes")` -> displays FALSE	`=IF(D3,"Pass","Fail")` -> displays Fail
4	Cara	82	no	`=AND(B4>=60,C4="yes")` -> displays FALSE	`=IF(D4,"Pass","Fail")` -> displays Fail
5	Dan	64	yes	`=AND(B5>=60,C5="yes")` -> displays TRUE	`=IF(D5,"Pass","Fail")` -> displays Pass
6	Eve	90	yes	`=AND(B6>=60,C6="yes")` -> displays TRUE	`=IF(D6,"Pass","Fail")` -> displays Pass
7	Finn	48	no	`=AND(B7>=60,C7="yes")` -> displays FALSE	`=IF(D7,"Pass","Fail")` -> displays Fail

Ana passes because both conditions hold. Ben fails on score alone, Cara fails on attendance alone, and Finn fails both. Dan and Eve clear both tests. The AND formula in D2 is the whole decision, and E2 simply relabels it.

More Examples

OR for either condition. To flag a student who meets the score bar or has good attendance, use =OR(B2>=60,C2="yes"). Ben returns TRUE here because his attendance is "yes" even though his score is 55.

NOT to invert a test. To mark everyone who is not eligible, use =NOT(D2). Ana returns FALSE and Ben returns TRUE.

Mixing AND with OR. To require a passing score plus either good attendance or a high score, use =AND(B2>=60,OR(C2="yes",B2>=85)). Eve clears it on both counts.

Boolean arrays in FILTER. The FILTER function filters an array based on a Boolean (True/False) array [2]. A single criterion looks like =FILTER(A5:D20,C5:C20=H2,""), which returns records matching the value in H2 [2]. For two criteria, Microsoft's documented pattern multiplies the boolean arrays: =FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"") returns rows that match both [2]. Multiplication acts as AND because TRUE times TRUE is 1, while any FALSE makes the product 0.

Boolean results inside IF. The IF function reads TRUE as a match and FALSE as no match, which is why =IF(D2,"Pass","Fail") needs no comparison of its own. If you want a numeric count instead, wrap the boolean in a function that sums it. For related patterns, see IF AND statements in Excel and the IF function step by step.

Comparison operators. The operators inside a boolean formula follow the same rules as standalone tests. The greater than or equal to operator is the one used throughout the worked example.

Errors and How to Fix Them

Error	Cause	Fix
#VALUE!	A logical argument is text that cannot be read as a condition	Check that each argument is a comparison or a cell holding TRUE or FALSE
Unexpected text match result	Extra spaces or hidden characters in the cell (case does not matter)	Trim the cell or compare against the exact stored text
#NAME?	Function name typed incorrectly	Use AND, OR and NOT with no spaces before the parenthesis
Unexpected FALSE	A number stored as text	Convert the text to a number before comparing
Error from FILTER (such as #VALUE!)	An include value is an error or cannot convert to Boolean	Clean the source column so every cell yields TRUE or FALSE [2]

Common Mistakes

Writing AND as a standalone statement. =AND(B2>=60) returns TRUE or FALSE but does nothing visible on its own. Fix it by wrapping the result in IF or another function that uses the value.
Using AND where OR is meant. =AND(B2>=60,C2="yes") fails anyone who misses one test. If either condition should be enough, switch to OR.
Forgetting quotes around text. C2=yes makes Excel look for a named range called yes and returns #NAME?. Write C2="yes" with quotation marks.
Comparing numbers stored as text. A score typed as text will not satisfy B2>=60. Convert the column to numbers first.
Assuming AND and OR accept only two arguments. Both accept many conditions, so you can test five or ten at once without nesting.
Mixing up NOT with a negative comparison. NOT(B2>=60) and B2<60 give the same result for numbers, but NOT also works on text comparisons and on other boolean formulas.

Limitations

Boolean formulas return only TRUE or FALSE, so they carry no detail about why a test failed. A FALSE from =AND(B2>=60,C2="yes") does not tell you whether the score, the attendance or both caused the failure. If you need that breakdown, test each condition in its own column.

AND and OR evaluate every argument, so an error in any argument propagates to the whole formula. Excel's logical functions do not short circuit the way some programming languages do [1]. In array formulas, a single unconvertible value can break the entire result, and FILTER returns an error when any include value is an error or cannot be converted to a Boolean [2].

Frequently Asked Questions

What is a boolean formula in Excel?

It is any formula that produces TRUE or FALSE. Comparisons such as B2>=60 are boolean on their own, and the logical functions AND, OR and NOT combine several comparisons into one result [1]. You can display that result directly or pass it to another function.

Can I use AND and OR in the same formula?

Yes. Nest one inside the other, as in =AND(B2>=60,OR(C2="yes",B2>=85)). Excel evaluates the inner function first, then uses its TRUE or FALSE as an argument to the outer one. Keep the parentheses balanced so each function closes properly.

Why does my boolean formula return 1 or 0 instead of TRUE or FALSE?

Some operations coerce Boolean values into numbers. When you multiply boolean arrays, as in the FILTER pattern (C5:C20=H1)*(A5:A20=H2), TRUE becomes 1 and FALSE becomes 0 [2]. The product is a number, and FILTER reads any nonzero value as included.

How do I count rows that meet a boolean condition?

Use a function that sums or counts the boolean results. Multiplying the boolean array by 1 converts TRUE and FALSE to 1 and 0, which you can then total. For text-based counting patterns, see COUNTIF not blank in Excel.

Does NOT work on text values?

Not on raw text, which returns #VALUE!, but it works on text comparisons. NOT reverses any value Excel can read as a Boolean, including the result of a text comparison such as C2="yes". NOT(C2="yes") returns TRUE when the cell holds anything other than "yes". For testing cell contents more broadly, the IS functions cover blank, number and error checks.

For a wider set of function patterns, the Excel formulas cheat sheet collects the common ones in one place.