Sumproduct values from two ranges

S

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.
 
R

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
 
B

Biff

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

I get a result of 387.5 using the same formula.

Biff
 
V

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
 
M

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top