How to SUM Numbers Starting 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 start 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 start 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 starting with the letter "L", please use the following formula:

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

The wildcard asterisk "*" in the formula is to represent all the letters after "L", and the result returns $171,000.00 for Leo and Lucas.

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

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

The wildcard asterisk "*" in the formula is to represent all the letters after "Ja", and the result returns $173,000.00 for James and Jack.

Formula 3: To get total salaries with names starting with any letter "Ja", but with two more letters, please use the following formula:

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

The wildcard question mark "?" in the formula is to represent the letters after "Ja". While one asterisk (*) represents all the letters, one question mark (?) represents only one letter. The result returns $94,000.00 for Jack.

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