Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
share|improve this question

4 Answers 4

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).

share|improve this answer

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

share|improve this answer

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

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

share|improve this answer

Use following formula!

Header: =UNIQUE(A:A)

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.