Count cells over n characters

To count cells that contain more than n characters, use a formula based on three Excel functions: SUMPRODUCT, LEN, and N.

The general formula to count cells over n characters:

=SUMPRODUCT(N(LEN(range)>n))

In the example, n=70 in cell F2, we find the cells which contain more than 70 characters. The result is 3 since three cells in the range B3:B9 contain more than n characters.

The formula in the cell F3:

=SUMPRODUCT(N(LEN(B3:B9)>F2))

How to count cells that contain more than n character

Explanation:

If you are working with Excel Formulas, it is worth analyzing from the inside out.

The LEN function runs on the range B3:B9. Because we select multiple values for argument, LEN returns multiple results in an array like this:

{54, 42, 66, 74, 73, 78, 63}

Evaluate the next part of the formula:

=LEN(B3:B9)>F2

The formula runs multiple tests using the “>F2” condition and returns an array containing TRUE or FALSE values. If the result is TRUE, the cell has more than 70 characters.

Use the N function to convert the TRUE and FALSE values to 0 or 1. If the result is 1, the cell contains more than n characters. Alternatively, you can use the double negative method to convert boolean expressions to numbers.

Now we have an array that contains only 0 and 1 values. To count the cells that met our criteria use the SUMPRODUCT function.

=SUMPRODUCT(N(LEN(B3:B9)>F2))

The formula above returns the sum of the products of the corresponding array and gets 3.

Use COUNTIFS to count numbers in an array

If you know how many characters are in a cell, you can use the COUNTIFS function. We will get the same result as above, but the formula is much simpler.

=COUNTIFS(C3:C9,">"&F2)

It is important to concatenate the greater than logical operator to F2 using double quotes “”.

Related Formulas:

• Count cells between two numbers
• Count cells that begin with a specific text