Group by Sum in Excel

Question

Let's assume that I have the following table in Excel

A     B
Item  quantity_sold
A     3
A     4
A     1
B     5
B     2
D     12
C     3
C     7
C     8

and I need to sum up quantity_sold grouped by Item and print the results on the adjacent column only once per group, similar to the following

A     B                 C   
Item  quantity_sold    SUM_by_item_type
A     3                 8
A     4
A     1
B     5                 7
B     2
D     12                12
C     3                 18
C     7
C     8

Is there any way I can achieve this without using Pivot Tables?

enter image description here

What do you have against pivot tables? – Stepan1010 Sep 18 '13 at 14:52 — Stepan1010, Sep 18 '13 at 14:52

score 22 · Accepted Answer · answered Sep 18 '13 at 11:04

22

Try this formula in C2 copied down

=IF(A2=A1,"",SUMIF(A:A,A2,B:B))

That will give you a sum on the first row of each group - other rows are left blank

answered Sep 18 '13 at 11:04

barry houdini

45,615
8
63
81

The formula above resulted in empty cell. No answers returned. What I am doing wrong? – Ahmadov Sep 18 '13 at 12:23
2

@Ahmedov, Barry's formula works if you have a title row (as in your examples. If you don't have a title row, as you show in your screenshot, then the formula in C1 would be `=IF(COUNTIF($A$1:A1,A1)>1,"",SUMIF(A:A,A1,B:B))` and that can be copied down as before – SeanC Sep 18 '13 at 13:41
5

It may depend on regional settings, e.g. some European countries require semi-colons in place of commas, e.g. `=IF(A2=A1;"";SUMIF(A:A;A2;B:B))` – barry houdini Apr 07 '14 at 12:24
Oh that is probably it, as "," is decimal delimiter. Thank you! – Flash Thunder Apr 07 '14 at 15:52
For me, it worked when I have title row and I changed "," to ";" – Oktay Mar 25 '16 at 14:47
I tried this but all I am getting is 0 in the output. I have some blank values in the column which I want to sum. Is it because of that? – Trisa Biswas Sep 14 '18 at 04:43

score 4 · Answer 2 · answered Sep 18 '13 at 11:03

I'd create a smaller table alongside (or on a different sheet) and use

=SUMIF(A:A,"A",B:B)

where...

=SUMIF(different-item-range,"what you're looking for",things-to-add-together)

=SUMIF is all explained here: http://office.microsoft.com/en-gb/excel-help/sumif-function-HP010062465.aspx

score 1 · Answer 3 · answered Oct 16 '17 at 06:20

Subtotal feature:

Click the Data tab in Excel's ribbon toolbar
Click the Sort button and sort by your category column
Click the Subtotal button and fill in the dialog as appropriate, then click OK

Video example: https://www.youtube.com/watch?v=OBj30n_x5aQ

Group by Sum in Excel

3 Answers3

Linked