Need to Sum from Multiple Tables

[fixthis]

Hi,

Need assistance in creating a summary table based on two criteria from an array of stacked tables one after another. The tables have been rearranged as suggested earlier (see link to other post - http://www.mrexcel.com/forum/excel-questions/808640-need-help-summing-two-way-lookups-multiple-tables.html. The tables are identical in pattern and format, so hopefully that would help in formulating a solution.


However, the tricky part is that each table is based on an engine type, which can change based on a drop down list. Changing the engine type, then changes all of the corresponding values in that individual table.

For my summary table (Evaluation Summary) at cell J17, I need the total tons from column G and L (on Engine Evaluation tab) corresponds to J15 (NOX) and that matches the type of engine in I17. I was only able to get the first occurrence, but I obviously need the other occurrence (i.e., cell G50). The second table on Evaluation Summary is an image of what some of the expected answers should be.

Please see attached file.

I am using Excel 2003.

 

[Kyle McGhee]

Hi,
See attached. I added a column on the second tab which is hopefully OK so that I use a SUMIF approach.

 

[Haseeb A]

Hello,

Try this in J17, then copy down & across.

=SUMPRODUCT((LOOKUP(ROW('Engine Evaluation'!$C$5:$C$136),ROW('Engine Evaluation'!$C$5:$C$136)/('Engine Evaluation'!$G$5:$G$136="# Tested"),'Engine Evaluation'!$C$5:$C$136)=$I17)*('Engine Evaluation'!$B$5:$B$136=J$15),'Engine Evaluation'!$G$5:$G$136)+SUMPRODUCT((LOOKUP(ROW('Engine Evaluation'!$C$5:$C$136),ROW('Engine Evaluation'!$C$5:$C$136)/('Engine Evaluation'!$L$5:$L$136="# Tested"),'Engine Evaluation'!$H$5:$H$136)=$I17)*('Engine Evaluation'!$B$5:$B$136=J$15),'Engine Evaluation'!$L$5:$L$136)

..a lengthy formula

 

[fixthis]

Kyle, your approach seems to work fine. I would prefer not to create helper columns, however, your idea about concatenating the two criteria may spur another approach (if possible) in placing the concatenation within the formula without the helper columns.

Haseeb, I tried your formula and I get #NA. Perhaps if you explain what your formula is supposed to do, that may help. Not sure why LOOKUP has two of the same Row range C5:C136 and then divide by "# Tested".

 

[NARAYANK991]

Hi ,

You can use Kyle's technique with the data in the two columns A and B coming through formulae. See the attached file.

Narayan

 

[fixthis]

Narayank991, that is another clever approach, although it still relies on helper columns. Interesting use of SUM(IF and SUMPRODUCT(IF. I think it does not make any difference in this case as I typed SUMPRODUCT in place of SUM and it worked.

 

[NARAYANK991]

Hi ,

I have deliberately not used SUMPRODUCT because when an IF is used inside a SUMPRODUCT function , the formula has to be entered as an array formula ; this is not desirable since normally a SUMPRODUCT formula does not need to be entered as an array formula.

Thus , in this case , whether you use SUM or SUMPRODUCT , the formula has to be entered using CTRL SHIFT ENTER , and of course , the result will be the same.

Narayan

 

[Haseeb A]

...I tried your formula and I get #NA...

In the first cell in the range G5:G136 must have "# Tested", other wise wil return #N/A. If your range is starting differ than G5, change it accordingly.

Please see attached.

 

[fixthis]

Thanks for the SUMPRODUCT vs SUM explanation.

Haseeb, thanks for carifying the need for the having the "# Tested" as the first cell in the range.

I am still having a bit of trouble understanding what LOOKUP(ROW,ROW/range="# Tested") does however.

 

[Tiago MF]

I am still having a bit of trouble understanding what LOOKUP(ROW,ROW/range="# Tested") does however.

I believe it's a workaround for the drop down lists you have on sheet "Engine Evaluation", so that if you choose other values on the lists, the formula still works. Also notice that the lookup doesn't stop there, it has a 3rd argument which is the result vector, without it the function doesn't work in the intended way.

It's easier to understand if you have less data, so just imagine that on sheet "Engine Evaluation" you only have the first table (i.e. range B4:L16); the LOOKUP will basically check what's in Cell C5 and then match with your criteria (Engine Type) on sheet "Evaluation Summary" (I17:I23). The way it does this is through relative position and that's why Haseeb said you must have "# Tested" on G5. The lookup will see which row has "# Tested" on column G and then return the value on the same row but on Colum C (i.e. value on C5). In the bigger picture, this is applied for all the tables you have on sheet Engine Evaluation, giving you the values on cells C5, C17, C29, etc on array format. You can see this if you copy the lookup to a blank cell and instead of ENTER press F9.

The rest of the formula is pretty straightforward if you understand how arrays work within sumproduct.

I fear my explanation skills aren't that good but I hope that is clear enough

 

[fixthis]

Tiago,

I appreciate your explanation, and follow some of it but not all of it. I generally understand the concept of array formulas, but fail to understand how the LOOKUP portion is forming an array of matches (as opposed to just finding the first occurrence like INDEX/MATCH would) and how that array is passed to the SUM function.

 

[NARAYANK991]

Hi ,

The reason for this is that the first parameter of the LOOKUP function is an array.

There are several Excel functions , such as MATCH and COUNTIF , which have a scalar ( single value ) as their first parameter , and which return a scalar as the output ; however , the Excel help never mentions that the first parameter can be an array , and in this case , the same function will return an array. Of course , in some cases , as in MATCH , the formula itself might have to be entered as an array formula , using CTRL SHIFT ENTER.

Try this with a few examples ; enter some data in a single-column range , and name it List ; now , enter the following formulae , and press F2 and F9 ; you should see an array of values :

=COUNTIF(List , List)

=MATCH(List , List , 0)

=LOOKUP(Row(List) , Row(List) , List)

Narayan

 

[Haseeb A]

Try as Narayan suggested, then you can see about array values.

....
I am still having a bit of trouble understanding what LOOKUP(ROW,ROW/range="# Tested") does however.

Let me try to explain my level best,

1. ROW('Engine Evaluation'!$C$5:$C$136)

This will give array of row numbers from 1 to 136 {5;6;7;8;9;10;11;12;13;…133;134;135;136}

2. 'Engine Evaluation'!$G$5:$G$136="# Tested"

This will give TRUE if G5:G136 value is = # Tested, otherwise FALSE. So will get array of TRUE or FALSE (TRUE = 1, FALSE = 0). {TRUE;FALSE;FALSE;FALSE;FALSE;…FALSE;FALSE}

3. ROW('Engine Evaluation'!$C$5:$C$136)/('Engine Evaluation'!$G$5:$G$136="# Tested")

Row numbers will divided by TRUE/FALSE values (TRUE = 1, FALSE = 0). If value is TRUE will give same row number, if FALSE will give an error #DIV/0! In array (Because dividing with zero) so will get array like {5;#DIV/0!;#DIV/0!;#DIV/0..;125;#DIV/0…..;#DIV/0!;#DIV/0!}

In Lookup_value we have used array numbers, so LOOKUP will look for each value (<=lookup_value) in lookup_vector.

1st lookup_value is 5, in lookup_vector we can see a value 5. So lookup will give 5

2nd lookup_value is 6, in lookup_vector can’t see 6. So LOOKUP will look for a value <=6, which is 5. So LOOKUP will take 5

3rd lookup_value is 7, so can’t see 7, so will take again 5 etc… for each lookup_value

etc...
In your sample 1st “# Tested” is in G5. 2nd one is in G17. So LOOKUP will take 5 as results for 5 to 16 lookup_values. So will get an array of 5’s {5;5;5;5;5;5;5;5;5;5;5;5;17….}

So the final array for each lookup_value will be like

{5;5;5;5;5;5;5;5;5;5;5;5;17;17;17;17;17;17;17;17;17;17;17;17;29;29;29;29;29;29;29;29;29;29;29;29;41;41;41;41;41;41;41;41;41;41;41;41;53;53;53;53;53;53;53;53;53;53;53;53;65;65;65;65;65;65;65;65;65;65;65;65;77;77;77;77;77;77;77;77;77;77;77;77;89;89;89;89;89;89;89;89;89;89;89;89;101;101;101;101;101;101;101;101;101;101;101;101;113;113;113;113;113;113;113;113;113;113;113;113;125;125;125;125;125;125;125;125;125;125;125;125}

4. 'Engine Evaluation'!$C$5:$C$136

This will give C5:C136 values, like;

{"PW4090";"Idle";"Fuel Usage @ Power Setting (gal)";…;"GENX1B7475P2";"Idle……;20.73259261584}

So first LOOKUP will look where is array values of lookup_value located in lookup_vector, then will take its corresponding values form result_vector.

So finally you will get array like;

{"PW4090";"PW4090";"PW4090;…..;"GENX1B7475P2";"GENX1B7475P2";"GENX1B7475P2"}

This means, you are putting all these values in the range C5:C136.

C5:C16 = PW4090
C17:C28 = PW4060
C29:C40 = CFM563C

Etc…
C25:C136 = GENX1B7475P2

So finally a SUMPRDUCT to sum only TRUE values.

I am sorry, if it is too complicated to understand or very lengthy. Hope this helps you.