To sum the cells that start with an exact, case-sensitive text string, you can use the SUMPRODUCT and EXACT functions.
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 case-sensitive "Jo"?
=SUMPRODUCT(–EXACT("Jo", LEFT(B2:B10, LEN("Jo"))),D2:D10)
The result returns 173.
Two people's last names start with "Jo": B3 and B7.
Explanation:
- Step 1: LEN("Jo"): To count the length of the text string of "Jo", which returns two;
- Step 2: LEFT(B2:B10, LEN("Jo")): To extract the first two letters;
- Step 3: EXACT("Jo", LEFT(B2:B10, LEN("Jo"))): To check if the first two letters are the same as "Jo"; The result returns TRUE when they are the same, and FALSE if not;
- Step 4: (–EXACT("Jo", LEFT(B2:B12, LEN("Jo")))): The double hyphen converts TRUE into one and FALSE into zero;
- Step 5: The SUMPRODUCT function returns the sum of the products of the new array and marks.
