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
``````
-

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
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. – Vaddadi Kartick yesterday
SImple :) =SORT(A2:B92;FALSE) – Zmogas 6 hours ago

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

-

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 the Pivot Table Report ... under the `Data` Menu.

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

-

Use following formula!

Header: `=UNIQUE(A:A)`

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

-