How to Sum Values Associated With Each Name in Excel
Question details
Calculate the total of the numbers associated with each repeated name.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains entries such as “joe 1” in individual cells, and the user wants one total per person.
- Observed behavior
- Repeated entries need to be grouped by name and their numeric values added together.
Check that each entry has a single-word name followed by a number, separated by a space. The example uses entries in A2:A6 and empty columns B, C, and E for the results.
Split the entries and sum each name with SUMIF
Use this method to add the actual values associated with each name, even when the numbers differ.
Copy A2:A6 and paste the entries into B2:B6. Leave column C empty for the numbers.
Select B2:B6, choose Data > Text to Columns, select Delimited, and choose Space as the delimiter. Finish the wizard to place names in column B and values in column C.
Enter each distinct name in column E, starting in E2. For example, enter joe in E2 and jim in E3.
In F2, enter =SUMIF($B$2:$B$6,E2,$C$2:$C$6), then drag the fill handle down alongside the names in column E.

Calculate totals by name in WPS Spreadsheets
Use Text to Columns and SUMIF in WPS Spreadsheets to separate name-and-number entries and add the values for each person.
- 1. Open the spreadsheet: Open your file in WPS Spreadsheets and copy the entries from A2:A6 into B2:B6, leaving column C empty.
- 2. Split the entries: Select B2:B6, choose Data > Text to Columns, and use a space delimiter to place names in column B and numbers in column C.
- 3. Enter distinct names: Type each name once in column E, starting at E2.
- 4. Sum the values: Enter =SUMIF($B$2:$B$6,E2,$C$2:$C$6) in F2 and drag the fill handle down for the other names.

Frequently Asked Questions
Can I use SUMIF if the names and numbers are already in separate columns?
Yes. If names are in B2:B6, values are in C2:C6, and the name to total is in E2, enter =SUMIF($B$2:$B$6,E2,$C$2:$C$6).
Why should I sum the values instead of counting the entries?
Counting only returns the number of matching entries. SUMIF adds their numeric values, so entries such as “joe 1” and “joe 3” produce a total of 4.
What if SUMIF returns zero even though the name is present?
Check that the values in column C are numbers, not text, and that the name in E2 matches the names in column B without extra spaces.
Will splitting on spaces work for full names?
Not with the simple split shown here: a name such as “Mary Jane” would occupy multiple columns. First separate the complete name from the final numeric value using a method suited to your data, then apply SUMIF.




