Group by Sum in Excel

excel sum grouping
Ahmedov picture Ahmedov · Sep 18, 2013 · Viewed 50.8k times · Source

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

Answer

barry houdini picture barry houdini · Sep 18, 2013

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

Related questions

Sum function in VBA

I have a problem with summing cells in vba. I need to use Cells(a,b): Range("A1").function="=SUM(Range(Cells(2,1),Cells(3,2)))" but it doesn't work.

Read More
How to SUM parts of a column which have same text value in different column in the same row

I have a column with names and a column with numbers: FirstName Name Number John Smith 17 John Smith 26 Peter Smith 116 Peter Smith 25 Franck Black 17 Luke Peterson 17 Luke Peterson 37 Names with same FirstName and Name represent the same person. I need …

Read More
Using SUMIFS with multiple AND OR conditions

I would like to create a succinct Excel formula that SUMS a column based on a set of AND conditions, plus a set of OR conditions. My Excel table contains the following data and I used defined names for the …

Read More