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).
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.
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
Use the Pivot Table Report ... under the Data Menu.
You will need to make sure that the first row has field names.
