# Sum values with same name

I have a Google Sheets with a long list of names and amounts. Many of the names are duplicates. For example:

``````John  | 10
Bill  | 2
Susan | 3
Frank | 4
Sally | 10
John  | 2
Susan | 2
John  | 3
``````

Is there any automated way to combine all of the values so I get a list without any duplicate names and a single sum of all of each person's entry?

For example:

``````John  | 15
Bill  | 2
Susan | 5
Sally | 10
``````

Assuming names are in A1:A8, amounts in B1:B8, first we need unique name list in D1:

``````=UNIQUE(A1:A8)
``````

Then use conditional sum in E1:

``````=SUMIF(A\$1:A\$8,D1,B\$1:B\$8)
``````

Please note `\$` prefix before cell number. This will keep the same ranges when we copy cell contents down (select cells E1:E8 and press Ctrl+D).

• Great! Just want to add that in some locales you need to replace comma with semi colon, i.e. =SUMIF(A\$1:A\$8;D1;B\$1:B\$8). Jan 17 '15 at 12:26
• Thanks for the answer. Upvoted. How do you sort it in descending order by the amount? In this example, John would come first, then Sally, Susan and finally Bill. May 4 '16 at 16:29
• SImple :) =SORT(A2:B92;FALSE) May 6 '16 at 9:00
• not working for me Jul 7 '16 at 15:17

If you use the Query Function then you don't have to worry about the list growing and not having copied the sum formula down far enough.

If names are in A:A and amounts are in B:B, then in D1 enter:

``````=QUERY(A:B, "select A, sum(B) group by A")
``````

There is a much more thorough answer here, with screenshots even.

• What if you had a header row? Would you have to know specific start and end row indices then? Aha, looks like I can specify the # of header rows...support.google.com/docs/answer/3093343?hl=en
– qix
Apr 12 '16 at 7:18
• This is the cleanest method, I think. Note that if you don't want to query the entire spreadsheet (for example, if you have unrelated data below), you can specify a range like A2:B28) Also, I like to use the CONCATENATE function to combine multiple columns from my spreadsheets into one row for grouping - like Lastname, Firstname. Aug 6 '19 at 2:45

I will give a shot at this as well: I prefer to use the filter function. Using column selection, allows for the table to automatically grow when adding "new" data:

``````FILTER(B:B;A:A=D2)
``````

If you're having a header, you can change it accordingly:

``````FILTER(\$B\$2:B;\$A\$2:A=D2)
``````

Making the subseqent summation is easy:

``````SUM(FILTER(\$B\$2:B;\$A\$2:A=D2))
``````

See example: File I prepared with example

• How do I make this formula auto expand to the bottom of the sheet? May 21 '20 at 12:41

Use the Pivot Table Report ... under the `Data` Menu.

You will need to make sure that the first row has field names.

• This is actually easier than the formula answers. Aug 3 '18 at 18:20
• Yep, this is the correct answer. Forget formulas for such a grouping task. Jul 1 '19 at 11:29

Use following formula!

Header: `=UNIQUE(A:A)`

Value: `=SUMIF(A:A,UNIQUE(A:A),B:B)`