X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> sum all records with 'ID'=A2    
post Aug 2 2002, 04:59 PM

Posts: 6
Joined: 4-July 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.
| | A | B | C | D | E |
|1| ID |Name| Date |Asset Purchased|AssetCost|
|2| 12|Joe |Yesterday|Cat | 3.00 |
|3| 13|Jane |Today |Fish | 2.00 |
|4| 12|Joe |Tomorrow |Dog | 8.00 |
|5|Total| | | | $13.00 |
| | A| B | C |
|1|ID|Name|Cost of Assets Purchased|
|2|12|Joe | |
|3|13|Jane | |
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.
Go to the top of the page
post Aug 2 2002, 08:21 PM

Posts: 1,114
Joined: 8-February 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.
Go to the top of the page
post Aug 3 2002, 06:15 PM

Author of post 100,000!
Posts: 261
Joined: 25-July 02
From: Melbourne, Australia

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:
--- ------- -------------------------
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.
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.
Brad Kennedy
Go to the top of the page
post Aug 6 2002, 10:25 AM

Posts: 6
Joined: 4-July 02
From: Canada

Dear Brad,
You're exactly right, it works perfectly!
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    20th January 2020 - 12:45 PM