How to Count Cells with Certain Lengths

In Excel, you can count the length of cells by using the LEN function, and it returns the number of characters. We can combine with the SUMPRODUCT function to get the number of cells that equals a certain length (e.g., 15)

Example: In the following database, the first name is in column A, the last name is in column B, and the email address is in column C.

Formula: To count the number of cells with the length of email addresses that is equal to 15.

=SUMPRODUCT((LEN(C2:C12)=15)*1)
=SUMPRODUCT(--(LEN(C2:C12)=15))

  • (LEN(C2:C12)=15) returns true if the length of the cell equals 15, and false if not 15.
  • The double hyphens (or times 1): to convert true into 1, and false into 0.
  • The SUMPRODUCT function returns the product of the new array.

Notes: The LEN Function

The LEN function returns the number of characters in a text string.

Formula:

=LEN(text)

Explanations:

– The text whose length you want to find, and spaces count as characters.

Leave a Reply