Sumproduct values from two ranges
 

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.

Sumproduct values from two ranges
 

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


"Steven Cheng" wrote:

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.

Sumproduct values from two ranges
 

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

I get a result of 387.5 using the same formula.

Biff

"Ron Coderre" wrote in message
...
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


"Steven Cheng" wrote:

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.

Sumproduct values from two ranges
 

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

Steven Cheng wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

Sumproduct values from two ranges
 

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)

"Steven Cheng" wrote:

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.