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

	Year 1	Year 2	Year 3
Car	2	3	4
Bike	4	6	7
Bus	8	9	10
Airplane	11	12	13

<tbody>
</tbody>

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

	Electric Car	Eletric Bus	Spaceship
Car	100%
Bike	50%	50%
Bus		100%
Airplane	10%	10%	80%

<tbody>
</tbody>

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

	Year 1	Year 2	Year 3	Year 4
Electric Car	10 HP	5 HP	5 HP	5 HP
Electric Bus	15 HP	10 HP	5 HP	5 HP
Spaceship	20 HP	10 HP	10 HP	5 HP

<tbody>
</tbody>

(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

	Year 1	Year 2	Year 3	Year 4
5 HP
10 HP	* example 1	** example 2
15 HP
20 HP

<tbody>
</tbody>

* 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?