Data Summary in new sheet

[taych]

I enter my data in Sheet1 and want a summary of the result in Sheet2 a
follows::

Sheet1
A B C D
1 Inv Name Prod Qty
2 100 ABC X1 10
3 100 ABC X2 20
4 100 ABC X3 30
5 101 BBC X1 40
6 101 BBC X2 20
7 102 CBC X1 50
8 102 CBC X2 40
9 102 CBC X3 30

Sheet2
A B C D E
1 Inv Name X1 X2 X3
2 100 ABC 10 20 30
3 101 BBC 40 20
4 102 CBC 50 40 30

I try to use vlookup formula but does not seems to work.
Please help, thanks

 

[Don Guillett]

try using sumproduct and copy down
=sumproduct((sheet1!rngB=b2)*(sheet1!rngC=$c$1)*sheet1!rngD)
where rngB is b2:bwhatever, rngD is d2:dwhatever

 

[Frank Kabel]

Hi
in addition to Don' answer I would take a look at pivot tables for
this:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm