# Google Spreadsheet: sum values with same name

I have a google spreadsheet 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
``````
-

## 5 Answers

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 at 7:18

Use following formula!

Header: `=UNIQUE(A:A)`

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

-

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

-

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). – JLund Jan 17 '15 at 12:26

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

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

-