Need Help Summing Two Way Lookups from Multiple Tables

[dwgnome]

Looking to sum the total emissions from several tables based on two criteria – the engine type and pollutant. However, each emission table is “labeled” by the engine type from a drop down (at C15, H15, Q15, V15 etc) and can change based on user input.

I was able to develop a formula to capture the correct emission (tons column) on the Engine Evaluation worksheet for pollutant NOX and engine type PW4090 at the first table approx C15:G26, but do not know how to extend to sum from all tables.

So at J17 on the Evaluation Summary I came up with:

[COLOR=#222222][FONT=Calibri][SIZE=3]=INDEX('Engine Evaluation'!B15:L26,MATCH(J15,'Engine Evaluation'!B15:B26,0),MATCH(I17,'Engine Evaluation'!B15:H15,0)+4)[/SIZE][/FONT][/COLOR]

Alternatively:

[COLOR=#222222][FONT=Calibri]=SUM(IF(('Engine Evaluation'!$B$22:$B$26=J15)*('Engine Evaluation'!$C$15:$F$15=I17),'Engine Evaluation'!$G$22:$G$26,0))[/FONT][/COLOR]

as Cntrl+Shft+Enter

Both give the correct answer but again, I don’t know how to extend the formulas to the other tables. Below the table with the one formula at J17 is an image of the table with a few of the expected results.

Please see tables at:https://dl.dropboxusercontent.com/u/95483456/Excel/DWGN%20Evaluation6.xls

Your help would be most appreciated. Excel 2003.

 

[dwgnome]

Still need help!

 

[dwgnome]

Bump.

 

[dwgnome]

Bump.

 

[dwgnome]

Bump.

 

[dwgnome]

Bump.

 

[Aladin Akyurek]

Arrange the tables in a vertical area one after the other, each having the same header structure. Once that done, summing across all of them would be an easy and reliable job.

 

[ififthelement]

Hi, I found an example on the internet for tables. Sorry I couldnt find upload button. but here is the example as is :

Use Col A1 to B and enter the Product table 1 and using similar structure create Product Table 2 and 3... one below the other

on Row 1 Product 1
$0.00	0.00%
$500.00	1.00%
$1,500.00	2.00%
$5,000.00	3.00%

<colgroup><col><col></colgroup><tbody>
</tbody>

Product table 2 - on Row 9
Product table 3 - on Row 17

towards the end of the 3 tables in Col A& B ..in row 23 create the table

Product 1	1
Product 2	2
Product 3	3

<colgroup><col><col></colgroup><tbody>
</tbody>

Now Create this table below in col D1 to H

Products	Amount of Sale	Table	Discount ?	Discount ?
Product 1	$3,225.00	1	64.5	64.5
Product 3	$2,014.00	3	20.14	20.14
Product 1	$1,264.00	1	12.64	12.64
Product 2	$1,434.00	2	21.51	21.51
Product 1	$2,295.00	1	45.9	45.9

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Formula for
F2 is =VLOOKUP(D2,$A$23:$B$25,2,0)
G2 is =E2*INDEX(($B$2:$B$5,$B$10:$B$13,$B$18:$B$21),MATCH(E2,$A$2:$A$5),,F2)
H2 is =E2*VLOOKUP(E2,CHOOSE(F2,$A$2:$B$5,$A$10:$B$13,$A$18:$B$21),2)

similarly drag it downwards

hope that helps..

 

[dwgnome]

Aladin,

I rearranged the tables as suggested. Please take a look at the updated file (link below) and let me know if this is what you had in mind. I have attempted to use a variety of approaches to capture and sum but to no avail.


Thank you ififthelement, but I am unable to understand the example you referenced. Maybe if you look at my file, you can work from that instead.


See updated file at: https://dl.dropboxusercontent.com/u/95483456/Excel/DWGN Evaluation7b.xls

 

[dwgnome]

Bump.