How to SUM Numbers That Contain Text (Case Sensitive)

To count case-sensitive text strings, you can use the SUMPRODUCT function and the EXACT function.

Example 1: The employee's name is in column A, the age is in column B, and the employees' salaries are in column C. You need to get the sum of the salaries that contain a text string with case sensitive from their names.

Formula 1: To get total salaries with names having the letter "Le", where L is upper case and e is lower case, please use the following formula:

=SUMPRODUCT((--ISNUMBER(FIND("Le",A2:A11))), C2:C11)

The FIND function is case-sensitive. If a name includes the text string of "Le", the result returns a number. Otherwise, it returns an error.

ISNUMBER transfers the array into true for numbers and false for others;

The double hyphens (--) turn true into 1 and false into 0;

The SUMPRODUCT function returns the sum of the products of the new array and salaries.

In the example, three employees' names have "Le", and the result returns $253,000.00 for A4, A6, and A8. Please note that cell A5 has "leo" and it should not include because the first letter is lowercase.

Example 2: The employee's name is in column A, the position is in column B, and the employees' salaries are in column C. You need to get the sum of the salaries that contain a text string with case sensitive from their names and positions.

Formula 2: To get total salaries with names having letters "Le", where L is upper case and e is lower case, and the position is a director or an assistant director.

=SUMPRODUCT((--ISNUMBER(FIND("Le",A2:A11))), (--ISNUMBER(FIND("Director",B2:B11))), C2:C11)

In the example, three employees' names have "Le" for A4, A6, and A8, and two employees are directors or assistant directors. The result returns $172,000.00 for Leon and WilliamLe.

Notes: The SUMPRODUCT function

The SUMPRODUCT function adds all the multiplication results for all arrays.

Formula:

=SUMPRODUCT(array1, [array2], …)

Explanations:

– Array1 is required, the first array is to multiply and then add.
– Array2 is optional, the second array is to multiply and then add.

Leave a Reply