Need Help Summing Two Way Lookups from Multiple Tables

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
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:

Code:
[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:
Code:
[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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441

ADVERTISEMENT

Bump.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

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

New Member
Joined
Sep 11, 2014
Messages
48
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 11
Product 22
Product 33


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

Now Create this table below in col D1 to H
ProductsAmount of SaleTableDiscount ?Discount ?
Product 1$3,225.00164.564.5
Product 3$2,014.00320.1420.14
Product 1$1,264.00112.6412.64
Product 2$1,434.00221.5121.51
Product 1$2,295.00145.945.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

Active Member
Joined
Dec 18, 2005
Messages
441
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,523
Messages
5,529,334
Members
409,863
Latest member
stacy09
Top