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?
The result returns 263. Three students have last names with eight characters B5, B6, and B10.
- 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.