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)
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:
Finally, summarize the 1s in the array using the SUM function:
=SUM(N(LEN(B3:B11)<8)) = 4Adding 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