You can use the LEN and SUMPRODUCT functions to sum the number of cells containing a text string with a certain length in another column.
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 are the sum marks for students' last names with eight characters and "Williams"?
=SUMPRODUCT(–(LEN(B2:B10)=8), –ISNUMBER(SEARCH("WILLIAMS", B2:B10)), D2:D10)
The result returns 180. Two people have last names with eight characters and "Williams".
In column B, there are four last names with eight characters. Please note that the SEARCH function is not case-sensitive, so both "WILLIAMS" and "Williams" are included.
Explanation:
- Step 1: LEN(B2:B10): To count the length of each cell;
- Step 2: (LEN(B2:B10)=8): If the length equals 8, the result returns TRUE; otherwise, it returns FALSE;
- Step 3: –(LEN(B2: B10)=8): The double hyphen converts TRUE into one and FALSE into zero;
- Step 4: SEARCH("WILLIAMS", B2:B10): To search the text location. The result returns a number if the cell contains the text; otherwise, it returns FALSE;
- Step 5: ISNUMBER(SEARCH("WILLIAMS", B2:B10)): The result returns TRUE if the cell contains the text, or FALSE if not;
- Step 6: –ISNUMBER(SEARCH("WILLIAMS", B2:B10)): The double hyphen converts TRUE into one and FALSE into zero;
- Step 7: The SUMPRODUCT function returns the sum of the products of the new arrays.
To count case-sensitive text, you can replace the SEARCH function with the FIND function. The FIND function is case-sensitive, so "WILLIAMS" is included but "Williams" is not.
For example, how many last names have eight characters and "WILLIAMS" (upper case)?
=SUMPRODUCT(–(LEN(B2:B12)=8), –ISNUMBER(FIND("WILLIAMS", B2:B12)), D2:D10)