Need help in sum data

brindha_shree

New Member
Joined
Sep 23, 2002
Messages
23
Hello,
The data below is each in different worksheet (where by the both data never should be all under one sheet)
I need to sum the savings according to the description.How can I do this?
One code could have more than one savings, example code 3060 have 2 savings. Then the one description
should have more than one code.I know how to sum the savings according to the code but I don’t know how to sum
the savings according to it's description.Is there anyone can help me?The data range is more than 25000 savings,
around 80 code and 15 description. Please help, I am a student doing my training. I need to submit this task soon.
Please Help. (the table above will be in diff sheets, the data cannot be mixed)

Regards,
Brindha


CODE SAVINGS
1245 833.69
1247 12217.84
1248 1211917
1249 119240.94
3030 904910.97
3040 4556.52
3050 185524.9
3060 514246.95
5001 354097.33
5003 624498.97
5015 796350
1245 132.85
3060 324.98
3060 2341.65
1248 2412
1247 12414

In other worksheet, this is the data


CODE Description
1245 equipment
1247 equipment
1248 equipment
1249 equipment
3030 Substrates
3040 Substrates
3050 Substrates
3060 Substrates
5001 Chemicals
5003 Chemicals
5015 Chemicals
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi - welcome to the board!

One way would be as follows:

1) associate each record in the main table with the appropriate description, based on the code for that record
2) Use a pivot table to summarise the data, based on the descriptions.


Set (1) can be done using a vlookup formula - see the example:
Book10
ABCDEF
1CODESAVINGSDerivedTypeCODEDescription
21245833.69equipment1245equipment
3124712217.84equipment1247equipment
412481211917equipment1248equipment
51249119240.9equipment1249equipment
63030904911Substrates3030Substrates
730404556.52Substrates3040Substrates
83050185524.9Substrates3050Substrates
93060514247Substrates3060Substrates
105001354097.3Chemicals5001Chemicals
115003624499Chemicals5003Chemicals
125015796350Chemicals5015Chemicals
131245132.85equipment
143060324.98Substrates
1530602341.65Substrates
1612482412equipment
17124712414equipment
18
Sheet3


If you then do a pivot table with descriptions in the rows & sum of savings in the data, you'll get:
Book10
GHIJ
1SumofSAVINGS
2DerivedTypeTotal
3Chemicals1774946.3
4equipment1359168.32
5Substrates1611905.97
6GrandTotal4746020.59
7
8
Sheet3


Does this help?

Paddy
This message was edited by PaddyD on 2002-09-24 21:05
 

brindha_shree

New Member
Joined
Sep 23, 2002
Messages
23
Hi,
Thanks for the help, I need to ask but the vlookup it returns N/A.(There is around 25 columns and the code colum is the 11th).I did it like what u show up above.

Is there any other options to do without combinig the two informations under one sheet?The savings shown is a monthly figure and i need to do the calculation for 12 months.
each month saving is saved under diff sheet, so that the users can easily view the data.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
1) post back with the formula you are using

2) You can continue with this method even if the data are spread through multiple sheets. Check the excel help file for 'About creating a PivotTable to consolidate multiple data ranges'.

Paddy
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS

ADVERTISEMENT

You also might try looking at the "consolidate" features. It works well when i use it for summarizing accounting stuff.
 

brindha_shree

New Member
Joined
Sep 23, 2002
Messages
23
Hello,
Thanks.Now I am trying to look what u guys suggest to me.Let me check it out first.
About the vlookup formula that i used
=VLOOKUP(L3,AB3:AC174,1,0)
L3 is the original data with the code
AB3:AC174 is the data i created (the code and the description like below....)

1273 Equipment
1290 Equipment
1390 Equipment

3010 Chemical and Gases
3020 Chemical and Gases
3030 Chemical and Gases
3040 Chemical and Gases
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
=VLOOKUP(L3,AB3:AC174,1,0)

Hhhmmmm...

1) you have not used absolute references to anchor the table range in the vlookup. This means that the table range will change as you copy the formula down. To fix this, change to:

=VLOOKUP(L3,$AB$3:$AC$174,1,0)

2) But, you are returning column 1 of the table?? If your table covers cells inb cols AB & AC, I assume you match your value in col L to the value in col Ab, & return the corresponding value in col AC. In which case:

=VLOOKUP(L3,$AB$3:$AC$174,2,0)

Paddy
 

brindha_shree

New Member
Joined
Sep 23, 2002
Messages
23
Hi there
the vlookup formula is still returning the N/A formula.

************JANUARY FEBRUARY MARCH
SUBCON
CHEMICAL
EQUIPMENT

I go through the conlidate just now, but it seems i can't get the output right.
This message was edited by brindha_shree on 2002-09-25 21:46
 

Forum statistics

Threads
1,144,741
Messages
5,726,017
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top