How to SUM Numbers That Cells are a Text String (Case Sensitive)

If you want to sum the numbers based on the text that is part of the cell, please check here. The following formula is to sum the numbers with the text string for full text in the cells.

Example 1: 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.

Formula 1: To get the total salaries for Leo (case sensitive)

=SUMPRODUCT((--EXACT("Leo",A2:A11)),C2:C11)

The EXACT function is case-sensitive. If a cell is "Leo", the result returns true. Otherwise, it returns false.

The double hyphens are to convert true to 1, and false to 0.

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

In the example, three employees' names are "Leo", and the result returns $266,000.00 for A4, A8, and A10. Please note that cell A5 is "leo" and it should not include because the first letter is lowercase. To get the sum without case sensitivity, please check here.

Formula 2: To get the total salaries for Leo, and the position is an analyst.

=SUMPRODUCT((--EXACT("Leo",A2:A11)), (--ISNUMBER(FIND("Analyst",B2:B11))), C2:C11)

In the example, three employees are "Leo" for A4, A8, and A10, and two of them are analysts. The result returns $176,000.00 for Leo in A8 and A10.

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