Sumproduct with two lists not in same order

Question

I have two lists that aren't in the same order but each list has a unique Identifier.

Table 1:

ID Commission Sales Group

100   $5       10     1 
200   $7       10     1 
300   $4       10     1 
400   $7       10     1 
500   $8       10     2 
600   $5       10     2 
700   $2       10     2

Table 2:

ID Commission Sales Group

100   $2       12     1 
300   $2       14     1 
400   $2       15     1 
600   $2       16     2 
500   $2       17     2 
700   $2       12     2

In this case I would need to Do the sumproduct of (Table 2 Sales) * (Table 1 Commission) when the group equals 1 (or 2 depending). The issue I am having is that the order isn't always the same on both tables and IDs drop out (but are never added back). Is there a way to a sumproduct between the two? I have hundreds of these tables and I need to multiply the Table i Sales by the Table i-1 Commission. I am also unable to sort the data as it would take a long time (not to mention things won't line up due to missing ID numbers). Any help would be appreciated. I am looking at historical data so I need to do this calculation a 100 times and show it all on a summary table along with other information.

The tables would be on sheets labeled 1,2,3 etc (was going to make my own indirect). and yes, it would only multiply the numbers where the IDS are the same. I would want to take the commission rate from table 1 (the end of the previous month) and multiply it by the sales of table 2 (the sales of the current month). I would want to sum up all the commissions for group 1 and group 2. For group 1 I would need to do ($5 * 12) for ID 100 +($4 * 14) for ID 300 + ($7*15) for ID 400 giving the group 1 total commission (ID 100,300,400) to be $221. I would then need to do the same thing for group 2.

Answer 1

In theory you can get the result for group 1, assuming Sales from table2, multiplied by commission from table1 for each relevant ID with the following formula

=SUMPRODUCT((Group2=1)+0,SUMIF(ID1,ID2,Commission1),Sales2)

Assumes all IDs are shown in table1 once and once only

.....but in your example in the question the commission shown against each ID is the same in each table, so if that was always the case you'd only need the data from table 2

Your example in comments doesn't make any sense to me, you have $7*13, for example, but there's no 13 in either table so I don't know where that comes from

Answer 2

This seems to be the work for a Pivot table