Excel Boolean logic

Boolean algebra is a mathematical system that represents logical expressions and relationships using only two values: TRUE and FALSE. Boolean logic refers to the principles that support Boolean algebra, including logical operations like AND, OR, and NOT and rules that govern the manipulation of logical expressions in Boolean algebra.

In the context of Excel, Boolean logic refers to a technique of building formulas to take advantage of the fact that TRUE can be represented by the number 1, and FALSE can be represented by the number 0. In fact any math operation in Excel will coerce TRUE and FALSE values into 1's and 0's automatically, as seen in the simple examples below:

=TRUE+0=1
=TRUE*1=1
=FALSE+0=0
=FALSE*1=0

Boolean logic can be used to simply formulas and eliminate the branching seen in nested IF formulas. In the example shown, the formula in cell E5 checks that group is "red" and score is > 80 like this:

=(C5>80)*(D5="red")
=(TRUE)*(TRUE)
=1

This is functionally equivalent to the formula below based on the IF function and the AND function:

=IF(AND(C5>80,D5="red"),1,0)

Boolean logic in array operations

Boolean logic often shows up inside other formulas and functions to perform array operations. The SUMPRODUCT function is a good example. To count cells in A1:A10 that are equal to "red" with SUMPRODUCT, you can use a formula like this:

=SUMPRODUCT(--(A1:A10="red"))

The double negative coerces the TRUE and FALSE values that result from the expression A1:A10="red" to 1s and 0s, and SUMPRODUCT then returns the sum of these numbers, which corresponds to the count of cells that contain "red". To sum numbers in B1:B10 when the value in A1:A10 is "red", you can use a formula like this:

=SUMPRODUCT(--(A1:A10="red")*B1:B10)

SUMPRODUCT has special features that make it especially suitable for this kind of formula in older versions of Excel. In the latest version of Excel, you can also use the SUM function like this: