Exceljet

Quick, clean, and to the point

How to convert booleans to numbers

In this video, we'll look at some ways you can convert TRUE and FALSE values in Excel to 1s and 0s.

When working with more advanced formulas, especially array formulas, you need to know how to convert TRUE and FALSE values in Excel to their numeric equivalents, 1 and 0.

This is best explained with an example.

In this worksheet, I have a list of fruit names.

Let's count all the names with more than 5 characters.

I'll first get the length of each name with the LEN function.

If I give LEN the entire range, results spill onto the worksheet into a dynamic array.

Now, to get a TRUE or FALSE result, I'll convert the formula to a logical expression that checks if the length is greater than 5.

=LEN(B5:B26)>5

The result is a set of boolean values. For each name, we get a result of TRUE or FALSE.

Now, if I feed this range into the SUM function, we get zero as the result.

Why is that?

This is because TRUE and FALSE are logicals, not numbers.

And the SUM function only works with numbers.

We need a way to convert TRUE and FALSE values to their numeric equivalents, 1 and zero.

A good way to do this is with any math operation.

I'll first wrap the formula in parentheses in order to control the order of operations. We want the numeric conversion to happen last.

One option is to simply add zero.

=(LEN(B5:B24)>5)+0

Or, I can multiply by 1

I can also use the N function which converts values to numbers.

<span style="display: none;"> </span><span style="display: none;"> </span>=(LEN(B5:B24)>5)*1

My personal favorite is to use a double negative, because it's easy to type, and visually, it signals a numeric conversion.

=--(LEN(B5:B24)>5)

But each method works fine.

In each case, we get either 1 or zero.

Now that we've converted the booleans to numbers, the SUM formula works properly, and we can see that 11 fruits have names with more than 5 characters.

Finally, I've been using a helper column to more easily visualize these operations, but typically, we'd put everything into a single formula.

I can just copy the formula from C5, and then paste into the SUM function.

=SUM(--(LEN(B5:B24)>5))

If I check the operation inside sum, with the F9 key, I can see that get the same array, and the SUM function returns the same result.

I can now remove the helper column, and everything will still work.

Dynamic Array Formulas are available in Excel 365 only.

Related shortcuts

Author 
Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.