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.