关于SUMPRODUCT函数，十一个案例带你熟练读懂这个函数

SUMPRODUCT 函数用于计算给定的几组数组中对应元素的乘积之和。换句话说SUMPRODUCT 函数先对各组数字中对应的数字进行乘法运算，然后再对乘积进行求和。

先看函数的语法结构：

Sumproduct(array1,[array2],[array3],...)，其中

array1：必需，表示一个数组

array2：可选，表示一个数组

array3...：可选，表示一个数组

注意事项：

• 参数可以由多个数组组成，但最多不能超过255个。
• 参数最少要有一个数组，就是说第一个参数是必需项。
• 多个参数时，数组必需具备相同的维度，否则Sumproduct函数返回错误。
• 会自动将非数值型数组元素按0来处理。

就是说这个函数将多个数组对应元素相乘，并返回乘积之和，如果只有一个数组，那么返回这个数组各元素之和。

如何来理解这个函数，我们先用一组数字来解释。

Sumproduct({1;2;3})：返回的是1+2+3=6 只有一个数组，返回各元素之和。

Sumproduct({1;2;3},{1;2;3})：返回的是1*1+2*2+3*3=1+4+9=14 两个数组，先乘积再求和。

Sumproduct({1;2;3},{1;2;3},{1;2;3})：返回的是1*1*1+2*2*2+3*3*3=1+8+27=36 三个数组，先乘积再求和。

以此类推。

接下来我们用详细的案例来了解这个函数在实际工作中的具体应用。

1. 一个参数求和

前面我们提到了SUMPRODUCT函数如果只有一个数组，直接返回各元素之和。

函数公式：=SUMPRODUCT(G2:G12)

此函数公式仅只有一个参数，直接返回G2:G12各单元格的和，等同于SUM求和G2:G12。

1. 两个参数求和

函数公式：=SUMPRODUCT(G2:G12,I2:I12)

此函数公式有2个参数，这两个参数的维度一样。

这两个数组之间的单元格先相乘，再将乘积求和。也就是每一行的数量乘以单价，最后再将每个积求和。

1. 三个参数求和

函数公式：=SUMPRODUCT(G2:G12,I2:I12,J2:J12)

此函数公式有3个参数，这三个参数的维度一样。

这三个数组之间的单元格先相乘，再将乘积求和。也就是每一行的数量乘以单价乘以提成率，最后再将每个积求和。

1. 单一条件求和

如图所示，将“产品编号=A010001”的所有产品汇总求和。

函数公式：=SUMPRODUCT((D2:D12= "A010001")*G2:G12)

这里先用D2:D12= "A010001"逐行判断产品编号，如果是返回TRUE，不是则返回FALSE，最终得到一个“TRUE或FALSE”组成的数组，因为不是直接返回的1或0，故用乘号连接第二个参数，将TRUE和FALSE转换成1和0，再分别与第二个参数相乘，最后汇总所有符合条件的数据。因为0乘以任何数得到的是0，故不符合条件的数据就不会被统计了。

1. 多条件求和

函数公式：=SUMPRODUCT((D2:D12= "A010001")*(F2:F12= "绿色")*G2:G12)

这个和上图例子类似，就是多了一个条件，按同样的方法设置第二个条件后，用乘号相连。如图所示，也就是将产品编号为A010001，颜色为绿色的产品汇总求和。

六、模糊匹配求和

需求：将产品编号里有3个0的产品汇总。

函数公式：=SUMPRODUCT((ISNUMBER(FIND("000",D2:D12))*G2:G12))

初看这个函数公式，函数比较多，但仔细看来，每个函数都特别简单，好理解。

先用FIND函数在数组D2:D12中查找“000”，如何查询到返回其位置数位，查询不到返回错误结果。

再用ISNUMBER判断FIND函数的结果是否是数字，其结果返回“TRUE和FALSE”的数组，作为SUMPRODUCT函数的第一个参数，与第二个参数先相乘再求和。

1. 单条件计数

提到按条件计算单元格的数目，大家都想到的是countif函数，其实用SUMPRODUCT一样好用。

函数公式：=SUMPRODUCT(N(F2:F12= "黄色"))

这里先逐行判断颜色是否等于黄色，是，返回TRUE,否则返回FASE，构成一个TRUE和FALSE的数组。如果直接使用SUMPRODUCT函数则会出错，为啥，前面注意事项里提到了一点“SUMPRODUCT函数会自动将非数值型数组元素按0来处理”。

在这里我们用N函数将不是数值形式的值转换成数值形式，也就是TRUE转换成1，FALSE转换成0。

当然除了N函数之外，我们也可以用“*1”或前面添加“—”的方式来转换，方法不是唯一的，看个人的喜欢。

需要注意的是如果用N("1")，得到的结果是0，因为“1”是文本，N函数将文本全部转换成0。

1. 多条件计数

除了COUNTIFS可以多条件计数之外，用SUMPRODUCT函数也是非常好用的。

函数公式：SUMPRODUCT((D2:D12="A010001")*(F2:F12="黄色"))

这里分别使用了两个条件逐行判断，中间用乘号直接将TRUE转换成1，将FALSE转换成0，最后得出两个条件都必须满足的数量个数。

1. 模糊查找计数

上面的例子也讲了，可以模糊查找汇总数量。同样地，也可以模糊查找计数。

函数公式：=SUMPRODUCT(N(ISNUMBER(FIND("000",D2:D12))))

这个函数公式同模糊查找汇总数量的公式，在这里就不详细讲述公式的含义了，大家可以参考上方的例子自行理解。

1. 横向统计

上方的例子都是针对列进行统计，接下来我们来看看如果是横向统计又该如何操作呢？

如图所示，需要统计每个产品四个月的总数量。

函数公式：=SUMPRODUCT(($C$2:$L$2=$M$2)*C3:L3)

有了上面的例子，要理解这个公式也不难，这就是一个条件汇总。先用$C$2:$L$2=$M$2，逐列判断标题是不是等于M2单元格，即数量，这里的标题行是固定的，添加绝对引用符号。

第二个参数选择下方的数据行，用乘号连接。这样就只汇总了数量列了。双击单元格，整列复制上公式。

1. 加权计算

这种情况运用的少，但也不是没有。

需求：根据表格里AB两个系列的平均数量与下方的占比重新计算总数量。

也就是将A系列的平均数量*70%与B系列的平均数量*30%汇总。

函数公式：=SUMPRODUCT($K$16:$L$16,K3:L3)

这里的难点是理清计算的思路，如果用第一行的数字来计算就是873.33*70%+944.67*30%，这也就是SUMPRODUCT函数的计算公式。这里唯一需要注意的就是第一个参数也就是占比数组需要添加绝对引用，这样双击复制公式才不会报错。

十一、表格的转换

如图所示，需要将左侧明细表格转换成右侧这个统计表格。

这个初看比较难，自己观察会发现，这个需求就是两个条件的汇总求和。只不过一个条件按列，一个条件按行，难的是添加绝对引用符号后整表拖动复制公式。

函数公式：=SUMPRODUCT(($C$2:$C$12=$L2)*($D$2:$D$12=M$1)*$G$2:$G$12)

怎么样，上方11个案例，如果你都弄得很清楚了，相信关于SUMPRODCUT函数，你也一定熟练掌握了。