Sum the difference in amounts if items are in two tables

[steinfm]

I have two tables that have data. One has estimated amounts and one has actual amounts. So, suppose I have a person (Steve) that we anticipate will buy oranges for $4 (Worksheet 1), but he actually buys the oranges for $5 (worksheet 2). The difference is $1. What I want to do is to add up all of the differences between worksheet 1 and worksheet 2, but only when the person on worksheet 2 matches worksheet 1. By that I mean, if we have Oscar on worksheet 1, but not on worksheet 2, we don't need to worry about him. To add to this, I also only want to add the difference if the currency is a certain currency (USD). I'm attaching a file so you can see.

Can someone help me with the right formula? I'm thinking sumproduct will work, but I don't understand that formula enough to make it work.

[myapple]

Hello steinfm,

A try within the joined file.
The formula in cell I2 (to drag down) is:

Please Login or Register  to view this content.

The formula in cell I1 is:

Please Login or Register  to view this content.

The custom cell format for the cells I2 to I15 is:

Please Login or Register  to view this content.

[steinfm]

Thank you for the input, but your solution requires that I record the formula in each row. The file is quite large and what I really want is not the individual line difference, but rather the total difference. How can I do it to just get the total? Will a sumifs work?

[:) Sixthsense :)]

Try in E2 cell of Estimate sheet

Please Login or Register  to view this content.

Drag it down.

Refer the attachment file for details.

Hope that helps!