Smarter Way to Sum from Multiple Tables

[dwgnome]

I am looking for a better way to sum values from multiple tables located on a different worksheet.I am using a series of IF statements but soon will run into the nested IF limitation as I need to expand the summary table to include many more tables located on the other worksheet called Engine Evaluation.


Currently the formulas in D17 and E17 etc work.See summary table below

Evaluation Summary

padding-right:2pt; "> " />

*	C	D	E
16	Engine	Engines Tested (annual)	Fuel Usage (annual)
17	PW4090	1	2,800
18	PW4077	1	2,800
19	PW4060	1	2,800
20	PW2000	1	2,800
21	CFM563C	2	5,600
22	PW3000	0	0
23	Other	*	*
24	Total	6	16,800

<colgroup><col style="width: 105px;"><col style="width: 80px;"><col style="width: 87px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D17 =IF(C17='Engine Evaluation'!C$15,'Engine Evaluation'!G$16)+IF(C17='Engine Evaluation'!H$15,'Engine Evaluation'!L$16)+IF(C17='Engine Evaluation'!C$30,'Engine Evaluation'!G$31)+IF(C17='Engine Evaluation'!H$30,'Engine Evaluation'!L$31)+IF(C17='Engine Evaluation'!C$45,'Engine Evaluation'!G$46)+IF(C17='Engine Evaluation'!H$45,'Engine Evaluation'!L$46) E17 =IF(C17='Engine Evaluation'!C$15,'Engine Evaluation'!G$18)+IF(C17='Engine Evaluation'!H$15,'Engine Evaluation'!L$18)+IF(C17='Engine Evaluation'!C$30,'Engine Evaluation'!G$33)+IF(C17='Engine Evaluation'!H$30,'Engine Evaluation'!L$33)+IF(C17='Engine Evaluation'!C$45,'Engine Evaluation'!G$48)+IF(C17='Engine Evaluation'!H$45,'Engine Evaluation'!L$48) D18 =IF(C18='Engine Evaluation'!C$15,'Engine Evaluation'!G$16)+IF(C18='Engine Evaluation'!H$15,'Engine Evaluation'!L$16)+IF(C18='Engine Evaluation'!C$30,'Engine Evaluation'!G$31)+IF(C18='Engine Evaluation'!H$30,'Engine Evaluation'!L$31)+IF(C18='Engine Evaluation'!C$45,'Engine Evaluation'!G$46)+IF(C18='Engine Evaluation'!H$45,'Engine Evaluation'!L$46) E18 =IF(C18='Engine Evaluation'!C$15,'Engine Evaluation'!G$18)+IF(C18='Engine Evaluation'!H$15,'Engine Evaluation'!L$18)+IF(C18='Engine Evaluation'!C$30,'Engine Evaluation'!G$33)+IF(C18='Engine Evaluation'!H$30,'Engine Evaluation'!L$33)+IF(C18='Engine Evaluation'!C$45,'Engine Evaluation'!G$48)+IF(C18='Engine Evaluation'!H$45,'Engine Evaluation'!L$48) D19 =IF(C19='Engine Evaluation'!C$15,'Engine Evaluation'!G$16)+IF(C19='Engine Evaluation'!H$15,'Engine Evaluation'!L$16)+IF(C19='Engine Evaluation'!C$30,'Engine Evaluation'!G$31)+IF(C19='Engine Evaluation'!H$30,'Engine Evaluation'!L$31)+IF(C19='Engine Evaluation'!C$45,'Engine Evaluation'!G$46)+IF(C19='Engine Evaluation'!H$45,'Engine Evaluation'!L$46) E19 =IF(C19='Engine Evaluation'!C$15,'Engine Evaluation'!G$18)+IF(C19='Engine Evaluation'!H$15,'Engine Evaluation'!L$18)+IF(C19='Engine Evaluation'!C$30,'Engine Evaluation'!G$33)+IF(C19='Engine Evaluation'!H$30,'Engine Evaluation'!L$33)+IF(C19='Engine Evaluation'!C$45,'Engine Evaluation'!G$48)+IF(C19='Engine Evaluation'!H$45,'Engine Evaluation'!L$48) D20 =IF(C20='Engine Evaluation'!C$15,'Engine Evaluation'!G$16)+IF(C20='Engine Evaluation'!H$15,'Engine Evaluation'!L$16)+IF(C20='Engine Evaluation'!C$30,'Engine Evaluation'!G$31)+IF(C20='Engine Evaluation'!H$30,'Engine Evaluation'!L$31)+IF(C20='Engine Evaluation'!C$45,'Engine Evaluation'!G$46)+IF(C20='Engine Evaluation'!H$45,'Engine Evaluation'!L$46) E20 =IF(C20='Engine Evaluation'!C$15,'Engine Evaluation'!G$18)+IF(C20='Engine Evaluation'!H$15,'Engine Evaluation'!L$18)+IF(C20='Engine Evaluation'!C$30,'Engine Evaluation'!G$33)+IF(C20='Engine Evaluation'!H$30,'Engine Evaluation'!L$33)+IF(C20='Engine Evaluation'!C$45,'Engine Evaluation'!G$48)+IF(C20='Engine Evaluation'!H$45,'Engine Evaluation'!L$48) D21 =IF(C21='Engine Evaluation'!C$15,'Engine Evaluation'!G$16)+IF(C21='Engine Evaluation'!H$15,'Engine Evaluation'!L$16)+IF(C21='Engine Evaluation'!C$30,'Engine Evaluation'!G$31)+IF(C21='Engine Evaluation'!H$30,'Engine Evaluation'!L$31)+IF(C21='Engine Evaluation'!C$45,'Engine Evaluation'!G$46)+IF(C21='Engine Evaluation'!H$45,'Engine Evaluation'!L$46) E21 =IF(C21='Engine Evaluation'!C$15,'Engine Evaluation'!G$18)+IF(C21='Engine Evaluation'!H$15,'Engine Evaluation'!L$18)+IF(C21='Engine Evaluation'!C$30,'Engine Evaluation'!G$33)+IF(C21='Engine Evaluation'!H$30,'Engine Evaluation'!L$33)+IF(C21='Engine Evaluation'!C$45,'Engine Evaluation'!G$48)+IF(C21='Engine Evaluation'!H$45,'Engine Evaluation'!L$48) D22 =IF(C22='Engine Evaluation'!C$15,'Engine Evaluation'!G$16)+IF(C22='Engine Evaluation'!H$15,'Engine Evaluation'!L$16)+IF(C22='Engine Evaluation'!C$30,'Engine Evaluation'!G$31)+IF(C22='Engine Evaluation'!H$30,'Engine Evaluation'!L$31)+IF(C22='Engine Evaluation'!C$45,'Engine Evaluation'!G$46)+IF(C22='Engine Evaluation'!H$45,'Engine Evaluation'!L$46) E22 =IF(C22='Engine Evaluation'!C$15,'Engine Evaluation'!G$18)+IF(C22='Engine Evaluation'!H$15,'Engine Evaluation'!L$18)+IF(C22='Engine Evaluation'!C$30,'Engine Evaluation'!G$33)+IF(C22='Engine Evaluation'!H$30,'Engine Evaluation'!L$33)+IF(C22='Engine Evaluation'!C$45,'Engine Evaluation'!G$48)+IF(C22='Engine Evaluation'!H$45,'Engine Evaluation'!L$48) D24 =SUM(D17:D23) E24 =SUM(E17:E23) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Below is an example of one table that covers two engines.On the actual worksheet there are several more of these tables below and another series to the right and going downwards.

Engine Evaluation

padding-right:2pt; "> " />

*	B	C	D	E	F	G	H	I	J	K	L
14	*	ENGINE MODEL	*	*	*	*	*	*	*	*	*
15	*	PW4090	*	*	*	# Tested	PW4077	*	*	*	# Tested
16	*	Idle	Approach	Climbout	Takeoff	1	Idle	Approach	Climbout	Takeoff	1
17	*	Fuel Usage @ Power Setting (gal)	*	*	*	Total (gals)	Fuel Usage @ Power Setting (gal)	*	*	*	Total (gals)
18	*	700	700	700	700	2,800	700	700	700	700	2,800
19	*	Fuel Usage @ Power Setting (lb)	*	*	*	*	Fuel Usage @ Power Setting (lb)	*	*	*	*
20	*	4,962	4,962	4,962	4,962	*	4,962	4,962	4,962	4,962	*
21	Pollutant	Emissions @ Power Setting (lb)	*	*	*	Total (tons)	Emissions @ Power Setting (lb)	*	*	*	Total (tons)
22	CO	102	2	1	1	0.05	100	2	0	0	0.05
23	VOC	11	0	0	0	0.01	15	1	0	0	0.01
24	NOX	21	65	212	303	0.30	21	56	161	197	0.22
25	SO2	2	2	2	2	0.00	2	2	2	2	0.00
26	PM	8	8	8	8	0.02	8	8	8	8	0.02

<colgroup><col style="width: 107px;"><col style="width: 72px;"><col style="width: 73px;"><col style="width: 76px;"><col style="width: 79px;"><col style="width: 89px;"><col style="width: 85px;"><col style="width: 80px;"><col style="width: 84px;"><col style="width: 72px;"><col style="width: 91px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Looking for a better way to capture and sum – perhaps using MATCH, LOOKUP or other functions or arrays.I cannot change the table layouts.Using Excel 2003.

 

[Vindaloo]

Cell D17:
=INDEX('Engine Evaluation'!$A$15:$DD$26,1+1,MATCH($C17,'Engine Evaluation'!$A$15:$DD$15,0)+4)

Cell E17:
=INDEX('Engine Evaluation'!$A$15:$DD$26,1+3,MATCH($C17,'Engine Evaluation'!$A$15:$DD$15,0)+4)


I set the width of the array as column DD. You can adjust accordingly. The "+" statements are my lazy OFFSETs from the relative location of the Engine name.

 

[dwgnome]

Thank you Vindaloo, I think you are on the right path in using the INDEX/MATCH array, however it does not capture the information in the tables below the one I showed starting at C30, C45, and Q15, V15. Your formulas only captured the first set. Total range would be C15 through Z15 and down to C56 and Z56 accordingly.

 

[Vindaloo]

I can't see the others, so here is what I think it would be

=INDEX('Engine Evaluation'!$A$15:$DD$1000,1+1,MATCH($C17,'Engine Evaluation'!$A$15:$DD$15,0)+4)

The first "+1" means 1 row down from where you found the engine name. The "+4" is the number of colums to the right of where you found the engine name. If you still have problems, post another table and I will show you this weekend. I can break down the INDEX & MATCH function if you need me to when the server isn't so slow.

 

[dwgnome]

That formula does not work as it only picks off the first occurrence. The 1+1 basically is 2 which means two rows down from A15 (the "offset"), the MATCH part looks for PW4090 in row 15, finds it 3 cells to the right of A15 and adds 4 more to get to the correct column. However it does not go any further to the other tables. I tried changing the range to A15:DD1000, but that did not work either. I do not have any data past column Z, so don't need DD.


Thanks.

 

[Vindaloo]

Can you post the tables so I can see them? I can't post code till later tonight. The 1+1 is the second row in the array. I guess I could have just plugged in a 2. The value 2 equates to row 2 in the array which is Row 16. The MATCH statement identifies the column in A15:Z15 that contains the engine name referenced by the first element of the MATCH statement. In other words, the Match finds C17 and tells you how many columns to the right the engine name shows up in the array A15:Z15. The statement in English is, Find the Engine name on Row 15, then find the point of intersection 1 row below the name and the 4th column to the right where the engine name = column 1, and return the value. The index is the entire range of the Coordinates (nth row, nth column) I can look at the tables tonight if you haven't figured it out.

 

[dwgnome]

I still don't see how your approach works.

Please see the actual file:

https://www.dropbox.com/s/onnrmoanj1...tion2.xls?dl=0

 

[dwgnome]

Bump.

 

[Vindaloo]

This time your tables are stacked vertically, so I matched by row. If there were headers on both axis, we would use 2 match statements.

Type	Engines	Fuel
PW4090	1	5,600
PW4077	#N/A	0
PW4060	1	2,800
PW2000	#N/A	2,800
CFM563C	1	5,600
PW3000	#N/A	0
Other
Total	#N/A	16,800

<colgroup><col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2929;"> <col width="90" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3185;"> <tbody> </tbody>

=INDEX('Engine Evaluation'!$C$15:$DD$1000,MATCH($C17,'Engine Evaluation'!$C$15:$C$1000,0)+1,5)

Although most problems can be solved with code, it doesn't hurt to get the person sending you raw data to be consistent. I still don't think I understand where your data is.

 

[dwgnome]

Were you able to see the actual file with the different tabbed worksheets? The data is on a different worksheet called Engine Evaluation. If you zoom out you can see the layout.