Smarter Way to Sum from Multiple Tables

D

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
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; "> " />
*CDE
16EngineEngines Tested (annual)Fuel Usage (annual)
17PW409012,800
18PW407712,800
19PW406012,800
20PW200012,800
21CFM563C25,600
22PW300000
23Other**
24Total616,800

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

Spreadsheet Formulas
CellFormula
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; "> " />
*BCDEFGHIJKL
14*ENGINE MODEL*********
15*PW4090***# TestedPW4077***# Tested
16*IdleApproachClimboutTakeoff1IdleApproachClimboutTakeoff1
17*Fuel Usage @ Power Setting (gal)***Total (gals)Fuel Usage @ Power Setting (gal)***Total (gals)
18*7007007007002,8007007007007002,800
19*Fuel Usage @ Power Setting (lb)****Fuel Usage @ Power Setting (lb)****
20*4,9624,9624,9624,962*4,9624,9624,9624,962*
21PollutantEmissions @ Power Setting (lb)***Total (tons)Emissions @ Power Setting (lb)***Total (tons)
22CO1022110.051002000.05
23VOC110000.01151000.01
24NOX21652123030.3021561611970.22
25SO222220.0022220.00
26PM88880.0288880.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.
 
Sort by date Sort by votes
Now I see it. The data could be in 4 different rows. You can SUMIF by a staggered column.
=SUMIF('Engine Evaluation'!$C$15:$C$200,$C17,'Engine Evaluation'!$G$16:$G$201)+SUMIF('Engine Evaluation'!$H$15:$H$200,$C17,'Engine Evaluation'!$L$16:$L$201)+SUMIF('Engine Evaluation'!$Q$15:$Q$200,$C17,'Engine Evaluation'!$U$16:$U$201)+SUMIF('Engine Evaluation'!$V$15:$V$200,$C17,'Engine Evaluation'!$Z$16:$Z$201)
This staggers the array to be summed by 1 row. Since Engine count is 1 row below the name, I start that part of the statement 1 row lower (16 vs 15).
<colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2531;" span="4"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;" span="3"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2730;"> <col width="70" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2474;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2673;"> <col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1024;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3697;"> <col width="64" style="width: 48pt;" span="4"> <col width="106" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3754;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 3982;"> <col width="64" style="width: 48pt;" span="7"> <tbody> </tbody>
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
In that case, this may also work for you. Depends what is in the intervening columns as I have not looked at your actual data.
Anyway, give it a try. :)

=SUMIF('Engine Evaluation'!$C$15:$V$200,C17,'Engine Evaluation'!$G$16:$Z$201)
 
Upvote 0
Both solutions work:)

Peter, yours is even more concise which is always desirable.

The formula
Code: 
=SUMPRODUCT(--('Engine Evaluation'!$C$15:$V$200=C17),'Engine Evaluation'!$G$16:$Z$201)

also seems to work as well. Not sure which one is more efficient.

Problem arises if C17 is empty. Instead of zero or blank, I get 198 (see the data file) using SUMIF, and 1,203,410 using SUMPRODUCT. I tried using the evaluate formula, but it skips over or does not show any details for SUMIF, But shows alot for SUMPRODUCT evaluation, so much so that I can't really see what is going on other than it is grabbing any value where there is a zero or blank in the first range.

I guess I can always place IF(ISBLANK(C17),"" statement to prevent that, unless you have a better idea.
 
Upvote 0
dwgnome said:
The formula
Code: 
=SUMPRODUCT(--('Engine Evaluation'!$C$15:$V$200=C17),'Engine Evaluation'!$G$16:$Z$201)

also seems to work as well. Not sure which one is more efficient.
Click to expand...
SUMIF is slightly faster, but we are only talking very small fractions of a second.




dwgnome said:
I guess I can always place IF(ISBLANK(C17),"" statement to prevent that, unless you have a better idea.
Click to expand...
That is the simplest solution.
.. or the same thing written a bit shorter:
=IF(C17="","",existing_formula)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
156
kerm007
K
esset
  • Solved
Splitting hours worked into multiple shift patterns
Replies
3
Views
551
esset
esset
B
  • Question
vba code / macro required to merge and add up data
Replies
0
Views
239
bryanrobson
B
M
  • Locked
  • Question
Alternative to nested If
Replies
1
Views
275
Joe4
Joe4
M
  • Solved
Alternative to nested IF Formula
2
Replies
11
Views
885
Fluff
Fluff

Forum statistics

Threads
1,214,956
Messages
6,122,465
Members
449,085
Latest member
ExcelError

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top