My Assistant
Custom Search

sum all records with 'ID'=A2 
Aug 2 2002, 04:59 PM Post#1  
Posts: 6 Joined: 4July 02 From: Canada  Dear Potential Saviour, Here's my situation, a bit simplified: I have two sheets, 'Assets' and 'Total' I want to take all assets purchased with an ID equal to Total!A2 and add them together putting the result in Total!C2 So, in Total!C2 I should have 3.00 + 8.00 = 11.00 I then want to fill down this equation in 'Total' so that each person has a total of everything they've purchased. The closest thing I can find to help me is the look up wizard which I can use to get the cost of one of Joe's purchased assets put in Total!C2 if I manually type in '12' , this won't fill down correctly, and it doesn't add up all his purchases. =VLOOKUP(12,Assets!$A$4:$E$6,MATCH("Asset Cost",Assets!$A$3:$E$3,)) I was thinking maybe I could substitute a variable for '12' in the equation which has the value of whatever Total!A2... but I need help. Is there some how a much simpler way to do this which I don't know of? If someone wants to see the whole issue, I'd be happy to email them this excel book. Assets   A  B  C  D  E  1 ID Name Date Asset PurchasedAssetCost 2 12Joe YesterdayCat  3.00  3 13Jane Today Fish  2.00  4 12Joe Tomorrow Dog  8.00  5Total    $13.00  Total   A B  C  1IDNameCost of Assets Purchased 212Joe   313Jane   I just happen to be the fellow in the office who knows a little bit more than the others about computers – so I have the joy of creating a spreadsheet which may be beyond my abilities. Thanks, Salt 
Aug 2 2002, 08:21 PM Post#2  
Posts: 1,114 Joined: 8February 02 From: California, USA  Use a pivot table. . Enter/select the range of data you wish to include (all rows and columns with data) 2. Drag the label ID to the "row" and label assetcost to the "column" portion of the pivot table layout 3. Specify the location you want the pivot table placed. Odon't know what version of Excel you are using; it will work from '97  XP, but the wizard is constructed a bit differently from version to version. You can also check the help files on pivot tables. If you want, I can DL the file and create the pivot table for you if you have problems with it. Just let me know the save version you need. Lil 
Aug 3 2002, 06:15 PM Post#3  
Author of post 100,000! Posts: 261 Joined: 25July 02 From: Melbourne, Australia  salted, I have included a file to help show you how to fix your problem. You can use the "SumIF" worksheet function to sum all the asset costs for each particular ID. On the totals sheet you have: ID NAME COST OF ASSESTS PURCHASED    12 Joe =SUMIF(Assets!A2:A4,Total!A2,Assets!E2:E4) 13 Jane << AS ABOVE >> If you open the help on "SUMIF" you'll see how it works. SUMIF(range,criteria,sum_range) The range is your range of ID numbers...Assets!A2:A4 The criteria is the id number from the totals sheet that you wish to sum on the assets sheet...Total!A2 The sum_range is the range on the assets sheet that contains the numbers you will be adding up...Assets!E2:E4 If you have trouble understanding this, or getting it to work, just email me and I will fill in the gaps. Regards, Brad Kennedy 
Aug 6 2002, 10:25 AM Post#4  
Posts: 6 Joined: 4July 02 From: Canada  Dear Brad, You're exactly right, it works perfectly! Thanks, Salt 
Custom Search

Search Top LoFi  7th December 2019  05:20 PM 