巧用sumproduct和vlookup完成多种类金额分类汇总

iamlaosong文

对于异常邮件在判定责任后按考核规定需要扣罚责任单位，情况不同，扣罚金额不同。现在有汇总的异常邮件明细，已经判定了责任单位和原因，现在要求各责任单位的扣罚总额。如果明细中有扣罚金额，那么分类汇总是很简单的事，用sumif函数和sumproduct函数均可以完成，现在是明细中只有原因，金额在一个对照表中。

明细表如下图所示：

要求生成下列统计结果（手机和省内两个明细表分别统计）：

首先想到的用sumproduct函数完成，但是不同项目如何整合到一个公式中去呢？最简单的办法是一个一个的统计，再加起来，即：

=SUMPRODUCT((手机!$J:$J=A3)*(手机!$K:$K=$J$3)*$K$3)
+SUMPRODUCT((手机!$J:$J=A3)*(手机!$K:$K=$J$4)*$K$4)
+SUMPRODUCT((手机!$J:$J=A3)*(手机!$K:$K=$J$5)*$K$5)
+SUMPRODUCT((手机!$J:$J=A3)*(手机!$K:$K=$J$6)*$K$6)
+SUMPRODUCT((手机!$J:$J=A3)*(手机!$K:$K=$J$7)*$K$7)
+SUMPRODUCT((手机!$J:$J=A3)*(手机!$K:$K=$J$8)*$K$8)
+SUMPRODUCT((手机!$J:$J=A3)*(手机!$K:$K=$J$9)*$K$9)
+SUMPRODUCT((手机!$J:$J=A3)*(手机!$K:$K=$J$10)*$K$10)

可是这个公式脑残不说，速度也慢，万一增加项目，公式还要改，后来想到用vlookup函数。首先想到的是用vlookup将扣罚金额加到明细中，这样汇总就简单了，但这样需要在明细表中增加公式，这是很不方便操作的，因为明细是不断变化的。经过思考和试验，找到了下面的公式：

=SUMPRODUCT((手机!$J:$J=A3)*VLOOKUP(手机!$K:$K,$J$3:$K$12,2,0))

多么简洁！如下图所示：