【干货】『SUMIF』『SUMIFS』『SUMPRODUCT』的区别及使用条件限制

前两章讲了SUM相关的函数运用，今天主要说一下SUMIF、SUMIFS、SUMPRODUCT之间的区别以及使用条件的限制。

SUM这个函数就不提了，后续大家看到更多的是数组相关的运算，而SUM本身是不支持数组运算的。

举个例子，表中有几个人若干月份的销售额，D列是求值条件，我们看一下能否通过SUMIF、SUMIFS、SUMPRODUCT根据给出的条件求值

条件1：A的销售总和

只有一个条件，三个函数都支持

在E2输入 =SUMIF(A2:A17,'A',C2:C17)，结果为 943

在F2输入 =SUMIFS(C2:C17,A2:A17,'A')，结果为 943

在G2输入 =SUMPRODUCT((A2:A17='A')*(C2:C17))，结果为 943

条件2：A，1月的销售额

那这里有两个条件，首先是A列姓名为A的，其次是B列月份为1的，SUMIF不支持

在F3输入 =SUMIFS(C2:C17,A2:A17,'A',B2:B17,1)，结果为 180

在G3输入 =SUMPRODUCT((A2:A17='A')*(B2:B17=1)*(C2:C17))，结果为 180

条件3：A，1月和3月的销售总额

这里也可以理解成两个条件，首先找出A列姓名为A的，其次B列月份为1的，这些数值求和；接着还是要找A列姓名为A的，但B列要找月份为3的，再把这些值求和；最后把两次求和的结果再求和。

实际上是操作了两次条件2的步骤，再操作一次求和。

SUMIF是不支持的，而SUMIFS本身只能求一组多条件的运算，所以在得到运算结果后还需要用SUM把结果相加。

在F4输入 =SUM(SUMIFS(C2:C17,A2:A17,'A',B2:B17,{1,3}))，结果为 801

在G4输入 =SUMPRODUCT((A2:A17='A')*(B2:B17={1,3})*(C2:C17))，结果为 801

这里月份需要用{}引起来代表数组，并且分割符号是  ，   这里是一个二维数组，不知道大家还记得之前将的数组概念吗 - -，忘记的话可以再看一下下图：

就是这样，红格显示结果，最后用SUM把两个红格的结果相加。

而SUMPRODUCT本身就是先乘积再求和，所以不需要加SUM。

条件4：A和C的销售额

这里看似两个条件，实际这两个条件在一个参数中，这里就和条件3中的有两个月份一样了，所以可以用SUMIF计算，当然结果还需要用SUM相加。

在E5输入 =SUM(SUMIF(A2:A17,{'A','C'},C2:C17))，结果为 2226

在F5输入 =SUM(SUMIFS(C2:C17,A2:A17,{'A','C'}))，结果为 2226

在G5输入 =SUMPRODUCT((A2:A17={'A','C'})*(C2:C17))，结果为 2226

条件5：A和C1月份的销售额

SUMIF不支持

在F6输入 =SUM(SUMIFS(C2:C17,A2:A17,{'A','C'},B2:B17,1))，结果为 879

在G6输入 =SUMPRODUCT((A2:A17={'A','C'})*(B2:B17=1)*(C2:C17))，结果为 879

条件6：A，1月和C，3月的销售总额

这里也是两个条件，并把结果相加，

在F7输入 =SUM(SUMIFS(C2:C17,A2:A17,{'A','C'},B2:B17,{1,3}))，结果为 449

在G7输入 =SUMPRODUCT((A2:A17={'A','C'})*(B2:B17={1,3})*(C2:C17))，结果为 449

条件7：A和C，1月和3月销售总额

那这里又多了一步，首先求出A和C，1月的总额，再求出A和C，3月的总额，最后相加。

实际和条件3有点类似，可以用下图来理解：

可以看到A和C是一个维度，1和3是一个维度，不同的维度。那这里SUMPRODUCT本身就不支持了，因为该函数中数组维度必须相同，不然找不到一一对应的元素。

有同学会问：那在外面加一个SUM函数不就可以了吗？答案是不可以，维度相同时SUMPRODUCT函数本身支持的条件之一，如果函数本身不支持，外面加一个SUM也是不行的。

但我们可以稍加转换一下，把SUM(WUMPRODUCT)变成PRODUCT PRODUCT

在F8输入 =SUM(SUMIFS(C2:C17,A2:A17,{'A','C'},B2:B17,{1;3}))，结果为 1769

在G8输入 =SUMPRODUCT(C2:C17*(A2:A17={'A','C'})*(B2:B17=1)) SUMPRODUCT(C2:C17*(A2:A17={'A','C'})*(B2:B17=3))，结果为 1769

可以看到，我们先求出1月的，再求出3月的，在用 号进行相加。

条件8：A和C，1月、3月、4月销售总额

这里和条件7是一样的，同样是两个维度，只不过其中一个维度中多了一个条件

在F9输入 =SUM(SUMIFS(C2:C17,A2:A17,{'A','C'},B2:B17,{1;3;4}))，结果为 2226

在G9输入 =SUMPRODUCT(C2:C17*(A2:A17='A')*(B2:B17={1,3,4})) SUMPRODUCT(C2:C17*(A2:A17='C')*(B2:B17={1,3,4}))，结果为 2226

当然了，这个结果和条件4的一样，月份只有这三个月。

最后这里月份中间用 ， 分开，原因还要要注意数组维度。

通过这次对比，可以看到SUMIFS和SUMPRODUCT使用范围相对来说比较广泛，SUMIFS相对SUMPRODUCT更易于理解，其中涉及到数组的概念也是很重要的，明白运算逻辑，更利于以后不同场景下多个函数交叉使用。