Sumproduct values from two ranges

[Steven Cheng]

I have two tables, both with two columns.

the first column in each table is a key index number. I want to be able to
multiple the values in the second column off when each record has the same
key value and then and return the summed value for the whole population.

eg.

Table 1

Index Rate
1 .03
2 .04
3 .03
4 .04
5 .02
6 .03

Table 2
2 1000
4 1500
5 2000
6 3000
1 2500
3 2750

the sumproduct/sum(if....) should return (2500 x .03) + (1000 x .04) + (2750
x .03) + (1500 x .04) + (2000 x .02) + (3000 x .03) = 306.50.

 

[Ron Coderre]

With your posted structure
Table_1 in A1:B7
Table_2 in A9:B15

This formula multiplies the principals in B10:B10 by the corresponding rates
in B2:B7 and sums those products
C1: =SUMPRODUCT(B10:B15*LOOKUP(A10:A15,A2:A7,B2:B7))

Note: for that approach to work, the rate table MUST have the indexes in
ascending order.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Biff]

the sumproduct/sum(if....) should return...... = 306.50.

I get a result of 387.5 using the same formula.

Biff

 

[via135 via OfficeKB.com]

yes!

(2500 x .03) + (1000 x .04) + (2750
x .03) + (1500 x .04) + (2000 x .02) + (3000 x .03)

should return
387.5 not
306.5

-via135

 

[mama no teeth]

Assuming your table 1 from A2:B7 and table 2 from D2:E7 and both tables are
sorting in ascending order

Formula in F1 =SUMPRODUCT(B2:B7,E2:E7)