Quick, clean, and to the point

Boolean logic

Example of formula using boolean logic

In the world of Boolean algebra, there are only two possible values for a math operation: 1 or 0.  Boolean logic refers to a technique of building formulas to take advantage of the fact that TRUE = 1, and FALSE = 0.  In Excel, any math operation will coerce TRUE and FALSE values into 1's and 0's.  In the example shown:


Boolean logic can be used to simply formulas and eliminate the branching seen in nested IF formulas. In the example the formula in E5 is equivalent to:


Boolean logic in other functions

Boolean logic often shows up inside other formulas and functions. 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:


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".

For another practical example of boolean logic, see INDEX and MATCH with multiple criteria.