Function Problems

How to Sum Values Associated With Each Name in Excel

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 868 views

Question details

Calculate the total of the numbers associated with each repeated name.

How to Sum Values Associated With Each Name in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Copy the entries

Copy A2:A6 and paste the entries into B2:B6. Leave column C empty for the numbers.

2
Separate names and 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.

3
List each name once

Enter each distinct name in column E, starting in E2. For example, enter joe in E2 and jim in E3.

4
Calculate the totals

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.

Split the entries and sum each name with SUMIF
Check the split values: Make sure column C contains numbers rather than text. Two entries of “joe 1” total 2; counting occurrences alone would not account for entries with different values.
Sum values by name

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. 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. 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. 3. Enter distinct names: Type each name once in column E, starting at E2.
  4. 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.
Sum numeric values for repeated names with SUMIFOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with spreadsheets using the free WPS Office suite
QA img-9

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.