If you want to sum the numbers based on the text that is part of the cell, please check here. The following formula sums the numbers based on the case-sensitive text string from another column.
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)
Explanation:
- Step 1: EXACT("Leo", A2:A11): The EXACT function is case-sensitive. If a cell is "Leo", the result returns true. Otherwise, it returns false.
- –EXACT("Leo", A2:A11): The double hyphens converts true to one and false to zero.
- 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 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 add.
– Array2 is optional; the second array is to multiply and add.