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.
