Site icon ExcelNotes

How to Sum Cells with a Text String and the Fixed Length

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:

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)

Exit mobile version