How to SUM Numbers Ending with 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 end with 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 end with 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 ending with the letter "h", please use the following formula:

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

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

Formula 2: To get total salaries with names ending with the letter "am", please use the following formula:

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

The wildcard asterisk "*" in the formula is to represent all the letters before "am", and the result returns $163,000.00 for William and Lucam.

Formula 3: To get total salaries with names ending with any letter "am", but with three more letters, please use the following formula:

=SUMIF(A2:A11,"???am",C2:C11)

The wildcard question mark "?" in the formula is to represent the letters before "am". While one asterisk (*) represents all the letters, one question mark (?) represents only one letter. The result returns $81,000.00 for Lucam.

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