Sumproduct with multiple criteria from a row or columm

[duduburton]

Guys,
I am an avid reader of this Excel forum and have learned much things here.
I have an advanced question regarding the SumProduct.

The problem is:
Let´s supose I have a plan to by for my company Cars, Bikes and Buses, with the quantity of that table below:

TABLE 1
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Year 3[/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Bike[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Bus[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Airplane[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]

However, I'm reestructuring the company and decide to replace most of transport for efficient type like (not buying the original ones):
So: the replacement idea will be like a table:

TABLE 2
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Electric Car[/TD]
[TD]Eletric Bus[/TD]
[TD]Spaceship[/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bike[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bus[/TD]
[TD][/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Airplane[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]80%[/TD]
[/TR]
</tbody>[/TABLE]

You can read the data like: for each 10 planes I planned to buy, if I want a new type of transportation I will by 1 Electric Car, 1 Electric Bus and 8 SpaceShips.

However, this substitution table have also a yearly base improvement on the horsepower:

TABLE 3
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Year 3[/TD]
[TD]Year 4[/TD]
[/TR]
[TR]
[TD]Electric Car[/TD]
[TD]10 HP[/TD]
[TD]5 HP[/TD]
[TD]5 HP[/TD]
[TD]5 HP[/TD]
[/TR]
[TR]
[TD]Electric Bus[/TD]
[TD]15 HP[/TD]
[TD]10 HP[/TD]
[TD]5 HP[/TD]
[TD]5 HP[/TD]
[/TR]
[TR]
[TD]Spaceship[/TD]
[TD]20 HP[/TD]
[TD]10 HP[/TD]
[TD]10 HP[/TD]
[TD]5 HP[/TD]
[/TR]
</tbody>[/TABLE]

(sorry, many details, I know):

What I want is to build a result table to find the number of transportation per horsepower per year that will made the following queries:

TABLE 4
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Year 3[/TD]
[TD]Year 4[/TD]
[/TR]
[TR]
[TD]5 HP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10 HP[/TD]
[TD]* example 1[/TD]
[TD]** example 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15 HP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20 HP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

* example 1
1) look for the HP label on the "A" column of TABLE 4 (found 10 HP)
2) seek for 10 HP in year 1 of TABLE 3 in Column "A" (found Electric Car)
3) Multiply the matrix of TABLE 2 per the Column of Year 1 in the TABLE 1 to find the number of Electric Car (100%*2 + 50% *4 + 100%*8)
4) this will be the number in example 1 cell.

** example 2
1) same procedure (found 10 HP)
2) however, we found 2 entries on TABLE 3, Electric Bus ans Space Ship
3) for that step, we will multiple the line of Year 2 from TABLE 1 per the Electric Bus and sum with the same operation for the SpaceShip
4) that will be the number.


I have tried to operate as this, but it does not work (reference, the formula is on cell B2 of Table4):

= SUMPRODUCT ((((TABLE4!A2=TABLE3!B2:B5)*(TABLE3!A2:A5))=TABLE2!A1:D1)*TABLE2!B2:D5*TABLE1!B2:B5)

the formula works well until the TABLE2!A1:D1 (I tried also with transpose and arrays functions), but in the case of exemple 2 (repeat data) we got a N/A error.

My doubt is that in the case of looking for the result of (TABLE4!A2=TABLE3!B2:B5)*(TABLE3!A2:A5))=TABLE2!A1:D1) but moved for the exemple 2, the operator should lead to to do a comparison criteria with 2 horse powers on TABLE2!A1:D1. But it does not.

Do you have suggestions?

 

[Aladin Akyurek]

The vector headed with Electric Car in TABLE 2 consists of: 100%, 50%, nil, 10%. If this must be multiplied with the Year 1 vector of TABLE 1, which conists of 2, 4, 8, 11, we would get: 100% * 2 + 50% * 4 + 0% * 8 + 10% * 11, a different multiplication than yours: 100%*2 + 50% *4 + 100%*8. Any comments?