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 theLEN function.
If I give LEN the entire range, results spill onto the worksheet into a dynamic array.
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 theSUM function, we get zero as the result.
This is because TRUE and FALSE are logicals, not numbers.
And the SUM function only works with numbers.
We need a way to convert these 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 to control the order of operations.
We want the numeric conversion to happen last.
One option is to simply add zero.
Or, I can multiply by 1
I can also use theN functionwhich converts values to numbers.
But each method works fine.
In each case, we get either 1 or zero.
I can just copy the formula from C5, and then paste into the SUM function.
I can now remove the helper column, and everything will still work.
