How to SUM Numbers That Contain Text

Using the SUMIF function, not only you can sum the numbers that meet the value criterion, but also you can sum the numbers that contain a text string from another variable. Please check below for more details.

Example: You are working with data that includes the employee's name, age, and salaries, and you need to get the sum of the salaries that contain some text from their names.

The employee's name is in column A, the age is in column B, and the employees' salaries are in column C.

Formula 1: To get total salaries with names having the letter "e", please use the following formula:

=SUMIF(A2:A11,"*E*",C2:C11)

The wildcard asterisk "*" in the formula is to represent all the letters before and after "e". The result returns $461,000.00 for Elijah, James, James, Henry, and Theodore.

Formula 2: To get total salaries with names having the letters "ah", please use the following formula:

=SUMIF(A2:A11,"*ah*",C2:C11)

The wildcard asterisk "*" in the formula is to represent all the letters before "ah". The result returns $163,000.00 for Elijah, Noah, and Jahk.

Notes: The SUMIF function

The SUMIF function adds all the numbers with the criteria you specified.

Formula:

=SUMIF(range, criteria, [sum_range])

Explanations:

- The range is required, which is the criteria range.
- The criterion is required, the criteria for the sum.
- Sum_range is optional, the cells to add together.

Leave a Reply