Learn how to convert boolean TRUE or FALSE to numbers (0 or 1) using the Excel N function or the double negative method.
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:
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 a 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 contain less than 8 characters.
Tip: You can not use the SUM function with booleans. TRUE and FALSE are logicals, 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. It is not necessary to insert a helper column; the formula will return 0 or 1 values.
Copy the formula down until cell B11:
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, simply multiply the expression by 1. Both operations return with numbers.
=(LEN(B5:B24)>5) +0 =(LEN(B3:B11)<8) * 1