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

[TABLE="width: 111"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]on Row 1
Product 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$0.00 [/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD="align: right"]$500.00 [/TD]
[TD="align: right"]1.00%[/TD]
[/TR]
[TR]
[TD="align: right"]$1,500.00 [/TD]
[TD="align: right"]2.00%[/TD]
[/TR]
[TR]
[TD="align: right"]$5,000.00 [/TD]
[TD="align: right"]3.00%[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="width: 111"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Product 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD="align: right"]3

[/TD]
[/TR]
</tbody>[/TABLE]

Now Create this table below in col D1 to H
[TABLE="width: 416"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Products[/TD]
[TD]Amount of Sale[/TD]
[TD]Table[/TD]
[TD]Discount ?[/TD]
[TD]Discount ?[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD="align: right"]$3,225.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]64.5[/TD]
[TD="align: right"]64.5[/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD="align: right"]$2,014.00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20.14[/TD]
[TD="align: right"]20.14[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD="align: right"]$1,264.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12.64[/TD]
[TD="align: right"]12.64[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD="align: right"]$1,434.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]21.51[/TD]
[TD="align: right"]21.51[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD="align: right"]$2,295.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]45.9[/TD]
[TD="align: right"]45.9[/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
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.