How to convert boolean TRUE and FALSE to 1 or 0

Learn how to convert boolean TRUE or FALSE to numbers (0 or 1) using the Excel N function or the double negative method.

When working with complex Excel formulas, you must know how to convert TRUE and FALSE values to the numeric equivalents 1 and 0. If you are unfamiliar with boolean logic, read this guide.

The Double negative method

We want to count all locations where the cell contains less than 8 characters. So, if you are looking for the fastest and easiest method for numeric conversion, take a look at the following formula:

=--(LEN(B3:B11)<8)
How-to-convert-boolean-TRUE-or-FALSE-to-1-or-0

The LEN functions return the length of the text in a given cell. We use the B3:B11 range as an argument, so the results spill into a dynamic array.

Excel evaluates the formula and returns 1 (TRUE) or 0 (FALSE) number values. The only thing to do using the SUM function is summarize the cells. The formula will return 4, the number of cells in a range where the cell contains less than 8 characters.

=SUM(--(LEN(B3:B11)<8))

Tip: You can not use the SUM function with booleans. TRUE and FALSE are logical, not number values. So, if you try to summarize a range that contains booleans, the result will be 0. So, don’t forget the conversion!

Convert Boolean TRUE and FALSE to numbers with the N function

In the second example, we will use a dynamic array to show the power of the N function without a double negative.

The table is the same as the case mentioned above. The N function uses a single argument (TRUE or FALSE) to convert booleans to numbers. Inserting a helper column is unnecessary; the formula will return 0 or 1 value.

Copy the formula down until cell B11:

=N(LEN(B3:B11)<8)

Result:

Convert-TRUE-and-FALSE-to-numbers-with-the-N-function

Finally, summarize the 1s in the array using the SUM function:

=SUM(N(LEN(B3:B11)<8)) = 4

Adding a zero or multiplying by 1

You can use math operations to convert booleans to numbers. The first method is to add a zero to the formula. To get 1 and 0 values instead of TRUE or FALSE logical values, multiply the expression by 1. Both operations return with numbers.

=(LEN(B5:B24)>5) +0
=(LEN(B3:B11)<8) * 1