How to Sum Numbers Ending an Exact Text String

You can use the SUMPRODUCT and EXACT functions to sum the cells that end with a case-sensitive text string.

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 with last names ending case-sensitive "ms"?

=SUMPRODUCT(--EXACT("ms", RIGHT(B2:B10, LEN("ms"))),D2:D10)

The result returns 87. One student's last name ending with "ms": B5.

Explanation:

  • Step 1: LEN("ms"): To count the length of the string of "ms", which returns one;
  • Step 2: RIGHT(B2:B10, LEN("ms")): To extract the last two letters from each cell;
  • Step 3: EXACT("ms", RIGHT(B2:B10, LEN("ms"))): To check whether the last two letters are the same as "ms". The result returns TRUE if they are the same and FALSE if not;
  • Step 4: (--EXACT("ms", RIGHT(B2:B10, LEN("ON")))): 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 the values in column D.

Leave a Reply