How to shorten this Google Spreadsheet formula?

Question

I've got a table in Google Spreadsheet like this:

Name | Amount
---------------------------
A1        0
A2        1
B1        2
B2        0

Now I've got another table like this:

Name | Component1 | Component2 | Component3 ...
_________________________________________________________________
A1          20             17             30
A2          10             20             15
B1          17             17             30
B2          123            19             43

Now I want a result table like this:

Name            |   Amount
________________________
Component1          44
Component2          54
Component3          75

So I want a result table that shows how which components you need. The formula for one cell would look like this:

AmountComponent1=AmountA1*Component1A1+AmountA2*Component1A2  // And so on...

Now I have a table with about 200 Products and about 10 Components. How Can I shorten this formula?

Answer 1

Short answer

Use SUMPRODUCT

Explanation

SUMPRODUCT

Calculates the sum of the products of corresponding entries in two equal-sized arrays or ranges.

Example

Assume that the list of the components, starts at A13. Add the following formula to B13 and fill down as necessary. It includes OFFSET to automatically select the second array.

=SUMPRODUCT($B$2:$B$5,OFFSET($A$8,0,Match(A13,$A$7:$D$7,0)-1,4,1))

Demo