How to Sum Numbers with Length

You can combine the LEN and SUMPRODUCT functions to sum the number of cells with a certain length.

Example: You are working with a worksheet with the first name in column A, the last name in column B, the grade in column C, and the marks in column D.

Question: What is the sum of the marks for students' last names with eight characters?

SUMPRODUCT(--(LEN(B2:B10)=8), D2:D10)

The result returns 263. Three students have last names with eight characters B5, B6, and B10.

Explanation:

  • Step 1: LEN(B2:B12): To count the length of each cell in the range;
  • Step 2: (LEN(B2:B12)=8): If the length equals 8, the result returns TRUE; otherwise, it returns FALSE;
  • Step 3: (--(LEN(B2:B12)=8)): The double hyphen converts TRUE into one and FALSE into zero;
  • Step 4: The SUMPRODUCT function returns the sum of the products of the new array and the range D2:D10.

Leave a Reply