SUMPRODUCT with two arrays and field selection

Hello,

I need to sum the product of two arrays.
In the first array the column number to use in multiplication is known, however in the second one the column to select depends on value from another column in first array.

Here is an example:

Array A:

Name	Amount	Final	Period
A	10	4	1
A	20	4	2
B	30	4	3

Array B:

Name	1	2	3
A	0.5	0.2	0.3
B	0.8	0.1	0.1

Finally for row number 1 in Array A i need to find corresponding value in Array B (matching Name and column header = Final - Period from Array A) and multiply it by Amount from Array A.
At the end the function should return a sum of all calculations, which for the example above is: 10 * ArrayB(A,3) + 20 * ArrayB(A,2) + 30 * ArrayB(B,1).

Is it possible to use standard formulas and functions to receive that?

Looking forward for your help

Originally Posted by ChemistB

Can you clarify a little more? So would that final formula simplify to
=10*(0.3) + 20*(0.2) + 30*(0.8)?

That's right - final result would be as quoted

Ok, I am one step further with the following formula:

Formula:

Please Login or Register  to view this content.

Unfortunately I am stuck again. Instead of 10*(0.3) + 20*(0.2) + 30*(0.8) the only result I get is 10*0.3 + 20*0.3 + 30*0.3 so those two matches in index are not moving properly

In Array A, at the end, add this formula:

Formula:

Please Login or Register  to view this content.

where $M$1:$P$21 is table of Array B.

then you just SUM that column

See attached

Thank you Guys.

The question is - is it possible to do the calculations and sum those values in the single cell? I mean with the array formula?

This simply adds my 3 formulae together:

=INDEX($B$8:$D$9,MATCH($A2,$A$8:$A$9,0),MATCH($C2-$D2,$B$7:$D$7,0))*$B2+INDEX($B$8:$D$9,MATCH($A3,$A$8:$A$9,0),MATCH($C3-$D3,$B$7:$D$7,0))*$B3+INDEX($B$8:$D$9,MATCH($A4,$A$8:$A$9,0),MATCH($C4-$D4,$B$7:$D$7,0))*$B4

Originally Posted by JohnTopley

This simply adds my 3 formulae together:

=INDEX($B$8:$D$9,MATCH($A2,$A$8:$A$9,0),MATCH($C2-$D2,$B$7:$D$7,0))*$B2+INDEX($B$8:$D$9,MATCH($A3,$A$8:$A$9,0),MATCH($C3-$D3,$B$7:$D$7,0))*$B3+INDEX($B$8:$D$9,MATCH($A4,$A$8:$A$9,0),MATCH($C4-$D4,$B$7:$D$7,0))*$B4

Having 1000 rows to sum would be problematic in this case I guess...

Any array will be problematic (if exist).
Adding one more column is much better in this case.
You can hide it if you want