Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mass Creation of Named Ranges? | Excel Discussion (Misc queries) | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
Sumproduct not working when summing values between two numbers | Excel Worksheet Functions | |||
How to slot cell values into pre-defined ranges | Excel Discussion (Misc queries) | |||
Test IF Two Numeric Values Match (from two separate Dynamic Ranges) | Excel Worksheet Functions |