Need help in sum data

[brindha_shree]

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

 

[PaddyD]

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
	A	B	C	D	E	F
1	CODE	SAVINGS	DerivedType		CODE	Description
2	1245	833.69	equipment		1245	equipment
3	1247	12217.84	equipment		1247	equipment
4	1248	1211917	equipment		1248	equipment
5	1249	119240.9	equipment		1249	equipment
6	3030	904911	Substrates		3030	Substrates
7	3040	4556.52	Substrates		3040	Substrates
8	3050	185524.9	Substrates		3050	Substrates
9	3060	514247	Substrates		3060	Substrates
10	5001	354097.3	Chemicals		5001	Chemicals
11	5003	624499	Chemicals		5003	Chemicals
12	5015	796350	Chemicals		5015	Chemicals
13	1245	132.85	equipment
14	3060	324.98	Substrates
15	3060	2341.65	Substrates
16	1248	2412	equipment
17	1247	12414	equipment
18
Sheet3

If you then do a pivot table with descriptions in the rows & sum of savings in the data, you'll get:

Book10
	G	H	I	J
1		SumofSAVINGS
2		DerivedType	Total
3		Chemicals	1774946.3
4		equipment	1359168.32
5		Substrates	1611905.97
6		GrandTotal	4746020.59
7
8
Sheet3

Does this help?

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

 

[brindha_shree]

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]

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]

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

 

[brindha_shree]

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]

=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]

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