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?

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

1 Answer 1


Short answer




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


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.




Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.