To sum values based on cells with a case-sensitive text string in another column, you can use the SUMPRODUCT function and the ISNUMBER 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)
Explanation:
- Step 1: FIND("Le", A2:A11): The FIND function is case-sensitive. If a name includes the text string "Le", the result returns a number. Otherwise, it returns an error;
- Step 2: ISNUMBER transfers the array into true for numbers and false for others;
- Step 3: The double hyphen (–) converts true into one and false into zero;
- Step 4: 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" which 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.
