Excel - how do I create a summary data sheet?

Question

I know how to create a pivot table but it isn't giving me what I need. I have a table of transactions:
Bill week 1 socks £20
Bill week 1 bread £15
Bill week 2 socks £12
Fred week 1 socks £16
Fred week 2 socks £8

I want to create a data sheet which is 'rolled up' on weeks:
Bill bread £15
Bill socks £22
Fred socks £24

A pivot table does this but Fred and Bill will appear on separate rows so it isn't a table that I can then do a VLOOKUP on. Any ideas how I can do this in Excel?

Answer 1

I took your data and entered it in columns A to D on sheet2 then entered the names and the items in a chart on sheet1



Bill Bread

Bill Socks

Fred Socks



In column C of sheet1 enter this formula and copy down the length of your data:



=SUMIFS(Sheet2!$D$1:$D$5,

Sheet2!$A$1:$A$5,A1,

Sheet2!$C$1:$C$5,B1)

Answer 2

Yes there is a way. All you must do is go to the sheet that has the data you wish to have to make use of whilst you style in a system. For illustration: Say sheet 1 has your knowledge and you want to calculate the sum of information in sheet 1 in sheet 2; just style =click on sheet 1, decide upon the info then return to sheet 2 and press enter.

Answer 3

http://support.microsoft.com/search/default.aspx?mode=a&query=summary+data+sheet&spid=global&catalog=LCID%3D1033&ast=25&ast=28&ast=29&ast=30&ast=31&ast=33&res=10