2

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 '16 at 1:31
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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