有两个不相同顺序的列表的Sumproduct

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

我有两个列表，它们不是相同的顺序，但是每个列表都有一个唯一的标识符。

Table 1:

表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:

表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.

在这种情况下，我需要在组等于1(或取决于2)时执行(表2销售额)*(表1佣金)的和积。我遇到的问题是，表和id上的顺序并不总是相同的(但是从来没有添加回)。这两者之间是否有一种相合的方法?我有数百个这样的表格，我需要将表I的销售额乘以表I -1的佣金。我也无法对数据进行排序，因为这将花费很长时间(更不用说由于缺少ID号而导致的事情不一致)。如有任何帮助，我们将不胜感激。我正在查看历史数据，所以我需要做这个计算100次，并将其全部显示在一个汇总表以及其他信息中。

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.

这些表格会在标有1,2,3等的表格上(这是我自己的间接表格)。是的，它只会乘以id相同的数。我想从表1(上月底)中提取佣金率，然后乘以表2(当月销售额)的销售额。我想总结一下第一组和第二组的所有佣金。对于第一组，我需要支付ID 100 +($4 * 14) ID 300 +($ 7*15) ID 400总佣金为$221。然后我需要对第二组做同样的事情。

2 个解决方案

#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

理论上，你可以得到第一组的结果，假设销售来自表2，乘以每个相关ID对应的表1的佣金，公式如下

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

= SUMPRODUCT((Group2 = 1)+ 0,条件求和(ID1,ID2 Commission1)Sales2)

Assumes all IDs are shown in table1 once and once only

假设所有id在表1中只显示一次

.....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

.....但在问题中的例子中，针对每个ID显示的佣金在每个表中都是相同的，因此，如果总是这样，您只需要表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

您在comments中的示例对我没有任何意义，例如，您有$7*13，但是两个表中都没有13，所以我不知道它从何而来

#2

This seems to be the work for a Pivot table

这似乎是主表的工作

#1