Sum of values multiplied by corresponding value in other table

RobbertH

Active Member
Joined
Apr 29, 2008
Messages
310
Hi,

I'm looking for a way to sum the results of values multiplied by values with a similar key.

Below a simplified Example:
sheet 1:
A B
1 P1 10
2 P2 15
3 P3 5
4 P4 1
5 P5 20
6 P6 10

sheet 2:
A B
1 -
2 P1 1.50
3 P2 2.00
4 P6 0.50
5 P3 1.00
6 P4 2.50
7 P5 3.00

Result should be: 117.5 (B1*E2+B2*E3+B3*E5+B4*E6+B5*E7+B6*E4)

Hope this is clear enough and you can help
 
Works like a charm! Thanks for your help.

One question, why did you change the declaration om Col from Integer to Long? It does indeed work but what is the difference?
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
One question, why did you change the declaration om Col from Integer to Long? It does indeed work but what is the difference?
Actually, you can remove the Dim statement for the X variable as my code does not make use of X at all. As for why I would make the change to Long... modern computers to not use 16-bit memory units anymore as the smallest memory unit is 32 bits, so Integer values are placed in the same sized memory unit as a Long... nothing is saved (anymore) by declaring variables as Integer. Also, not using Integer variables avoids a minor side effect associated with Integers... the 32767 memory boundary. What do I mean by that? Try this code and, before you run it, decide if it will work or not, then run it and see how your guess turned out...

Code:
Sub IntegerBoundaryProblemDemo()
  
  Dim Var1 As Integer
  Dim Var2 As Integer
  Dim Var3 As Long
  
  Var1 = 150
  Var2 = 225
  
  Var3 = Var1 * Var2
  
  MsgBox Var3

End Sub
 
Upvote 0
I may be missing something but I think you could also use a formula for this, i.e.

=SUMPRODUCT(Sheet1!B1:B6,SUMIF(Sheet2!A1:A7,Sheet1!A1:A6,Sheet2!B1:B7))

That will work with differently sized tables and/or empty rows in either table
 
Upvote 0
I may be missing something but I think you could also use a formula for this, i.e.

=SUMPRODUCT(Sheet1!B1:B6,SUMIF(Sheet2!A1:A7,Sheet1!A1:A6,Sheet2!B1:B7))

That will work with differently sized tables and/or empty rows in either table

Does that take into account that the order of the keys (Column A values) may be different on each sheet?
 
Upvote 0
Yes, Rick - the order shouldn't be important - I get the correct 117.5 result using that formula on the example data, even where the key order differs. It also still works if some table 1 keys are not present in table 2
 
Last edited:
Upvote 0
Yes, Rick - the order shouldn't be important - I get the correct 117.5 result using that formula on the example data, even where the key order differs. It also still works if some table 1 keys are not present in table 2
Sorry, it is my fault. I tried applying your formula in set of test sheets I had set up at the time and it gave me a different answer than my code did, so, on a quick look, I thought I had identified the problem as the out-of-order elements; hence, my posted question. However, I just reset up my test sheets again and now I get your formula outputting the same values as my code. I do not know what I had done wrong initially, but it is apparent I made a mistake somewhere because your formula is now working perfectly in the new test-bed I set up. So... nice going with that formula!
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top