# 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 “”. Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.