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 that 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

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}

Workaround-for-count-cells-that-contain-more-than-n-character

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.

the-formula-creates-an-array-of-TRUE-FALSE-values

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.

To-convert-the-TRUE-and-FALSE-values-to-0-or-1-use-the-N-function

Now, we have an array containing only 0 and 1 values. Use the SUMPRODUCT function to count the cells that meet our criteria.

=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, but the formula is much simpler.

count-cells-over-n-characters-using-countifs

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

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

Related Formulas: