Count cells over n characters

In this example, the goal is to count the number of cells in a range that are over a certain number of characters in length, where the number (n) is provided as a variable in cell F4. This problem can be solved with the SUMPRODUCT and LEN functions like this:

=SUMPRODUCT(N(LEN(B5:B15)>F4)) // returns 5

The formula returns 5 since there are five cells in B5:B15 that contain more than 40 characters.

Reference calculation

The formula in C5, copied down, is based on the LEN function:

=LEN(B5) // returns 25

This calculation is provided for reference only and is not used by the formula above. The counts in column C make it easy to quickly check results.

Checking length

Working from the inside out, the number of characters in each cell is calculated with the LEN function like this:

LEN(B5:B15)

The LEN function runs on the range B5:B15. Because we give LEN multiple values, it returns multiple results in an array like this:

{25;47;46;45;42;32;36;34;36;40;46}

Each number in the array is the length of a cell in B5:B15. This array is evaluated with the logical expression ">F4", which creates an array of TRUE FALSE values:

{FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}

Each TRUE corresponds to a cell that contains more than 40 characters, since cell F4 contains 40. To convert the TRUE and FALSE values to their numeric equivalents, we use the N function:

N({FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE})

Note: the double negative (--) is another way to convert TRUE/FALSE to 1/0.

The result is an array of 1s and 0s:

{0;1;1;1;1;0;0;0;0;0;1}

Counting results

This array is returned directly to the SUMPRODUCT function, which returns the sum of numbers in the array:

=SUMPRODUCT({0;1;1;1;1;0;0;0;0;0;1}) // returns 5

The final result is 5. Since n is provided as a variable in cell F4, it can be changed at any time and the formula will recalculate and return a new result.

COUNTIFS function

This is an example of a problem that can't be solved directly with the COUNTIFS function. This is because COUNTIFS requires a range and won't allow processing of an array like that returned by the LEN function above. However, if you don't mind using a helper column, you could use COUNTIFS on column C like this:

=COUNTIFS(C5:C15,">"&F4) // returns 5

The result is the same as the SUMPRODUCT formula above. Note the greater than operator (>) is enclosed in double quotes ("") and concatenated to F4.