group cells....

[R-M]

Hi

I've following information wanted to group by first
column and show the sum of quantities:


a b c
---- ----- ------
123 10.8
123 20.2
123 44
124 2
124 11
124 45


The desired result:

a b c
---- ----- ------
123 10.8 75 -> sum(10.8+20.2+44)
123 20.2
123 44
124 2 58 -> sum(2+11+45+0)
124 11
124 45
124 0


note: I don't want to write sum(a1:a3) , ... because
it depends on the number of rows has the same value.

any help would greatly appricieted.

 

[Harald Staff]

Hi

Use a Pivot table for that:
http://www.cpearson.com/excel/pivots.htm

 

[Murthy]

Though Pivot Table is the best option, it helps to know the SUMPRODUCT
function also.

The results can be obtained in your case with a formula like this:

=SUMPRODUCT((A1:A100="123")*(B1:B100))

You can substitute "123" with a cell reference also.

Regards,
Murthy