62

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
Improve this question

5 Answers 5

Reset to default
65

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

Improve this answer
4
  • 2
    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, 2015 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.
    – Kartick Vaddadi
    May 4, 2016 at 16:29
  • SImple :) =SORT(A2:B92;FALSE)
    – Zmogas
    May 6, 2016 at 9:00
  • 3
    not working for me
    – Gilbou
    Jul 7, 2016 at 15:17
34

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.

Improve this answer
2
  • 1
    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, 2016 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.
    – Evan Donovan
    Aug 6, 2019 at 2:45
10

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

Improve this answer
1
  • How do I make this formula auto expand to the bottom of the sheet?
    – borisdiakur
    May 21, 2020 at 12:41
7

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

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

Improve this answer
2
  • This is actually easier than the formula answers.
    – Dannid
    Aug 3, 2018 at 18:20
  • Yep, this is the correct answer. Forget formulas for such a grouping task.
    – giorgio79
    Jul 1, 2019 at 11:29
1

Use following formula!

Header: =UNIQUE(A:A)

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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