Count cells over 100 characters

★ 20 minutes read

Just as it is with using other formulas in Excel to solve many different issues, you can also use Excel to count cells over 100 characters. This post will guide you on how to count cells with over 100 characters using a formula approach in Excel. To be able to do learn this, you will get familiar with SUMPRODUCT and LEN functions.

Count cells over 100 characters

Formula

The general formula showing how to Count cells with over 100 characters is:

=SUMPRODUCT(N(LEN(array)>100))

Explanation

A formula based on N, SUMPRODUCT AND LEN functions can be used to count the number of cells that have over a specific number of characters.

Example 1

 

 

Figure 1: Count cells over 100 characters

In figure 1 above, the formula in cell C4 is:

=SUMPRODUCT(N(LEN(B7:B11)>100))

Starting from the inside, the LEN function works on the range B7:B11. And since we present LEN with multiple values, it will likewise return multiple results in a range thus:

(86; 168; 117; 147; 83)

The range above is further evaluated against the logical expression >100, which gives an array of TRUE and FALSE values as below.

{FALSE, TRUE, TRUE, TRUE, FALSE} = {0, 1, 1, 1, 0}

The N function converts these values to 0’s and 1’s, and the SUMPRODUCT further process them to return 3.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar