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

• The AmountCompont3 result is wrong, I think. It should be 55 instead of 75. – Rubén Aug 23 '16 at 1:31

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