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?
Best Answer
Short answer
Use SUMPRODUCT
Explanation
SUMPRODUCT
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.
Demo