如何用SUMPRODUCT计算所有匹配指定项目的行的乘积和

问题根源 #

你当前的公式里，MATCH函数只会返回第一个匹配到目标item的行号，因此SUMPRODUCT仅计算了第一行的# per unit * quantity，无法汇总所有重复项的乘积。

解决方案 #

方案1：兼容所有Excel版本（SUMPRODUCT写法） #

在Supplies表的in stock列（如C2单元格）输入以下公式，下拉填充即可：

=SUMPRODUCT(('Expenses'!$B$2:$B=B2)*'Expenses'!$C$2:$C*'Expenses'!$D$2:$D)

• 原理：('Expenses'!$B$2:$B=B2)会生成一个布尔数组，匹配当前item的行对应TRUE（数值1），不匹配则为FALSE（数值0）；
• 数组与# per unit（C列）、quantity（D列）相乘后，仅保留匹配项的乘积；
• SUMPRODUCT自动汇总所有有效乘积，得到总库存数。

方案2：适用于Excel 365/2021（动态数组写法） #

如果你的Excel版本支持动态数组函数，可使用更直观的SUM+FILTER组合：

=SUM(FILTER('Expenses'!$C$2:$C*'Expenses'!$D$2:$D, 'Expenses'!$B$2:$B=B2))

• 原理：FILTER直接筛选出Expenses表中item与当前B2一致的所有行，计算这些行的# per unit * quantity；
• SUM对筛选后的结果求和，自动忽略无匹配的情况（返回0）。

验证示例 #

针对你给出的测试数据：

• Expenses表中yarn, blue的两行乘积分别为1*4=4和3*3=9，公式计算总和为13，与预期一致；
• yarn, red的乘积2*3=6、buttons, black的乘积2*12=24，均能正确汇总。

内容的提问来源于stack exchange，提问作者sunflower_fields_forever