How to Sum Numbers Starting an Exact Text String

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.

Leave a Reply