函数系列：SUMPRODUCT解决四大类统计问题，堪称万能统计函数

——这是第288篇教程——

函数是表格中最简单的部分，

因为每个公式都是有规律的，按格式套用就可以了；

函数也是表格中最难的部分

因为数目多，目前函数有12类，405个，还会随着版本而增加

而且函数还能排列组合使用。

        所有内容均为原创，全文带公众号转载，随意+无限欢迎，如复制在自己的公众号上面转载请与本公众号联系，码字不易，敬请尊重啊！

您看此文用  · 秒，转发只需1秒

本期教程知识点：

之前介绍过的SUM系列函数有：SUM、SUMIF与SUMIFS（具体内容可参考：SUMIF 与 SUMIFS的使用，又一个实用贴，今天再说一个SUMPRODUCT函数，SUMPRODUCT函数可以代替前面的三种函数，还能替代countif等函数，而且字符数额单元格默认为0或可计算。可解决四大类统计问题。

• SUMPRODUCT函数语法、原则

1.求和：单条件、多条件、字符型数据求和；

2.计数：单条件、多条件；

3.多权重计算

4.中国式排名

· SUMPRODUCT函数语法、原则

定义：

---

• SUMPRODUC：

  在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

• 语法：SUMPRODUCT（array1,array2,array3, ...）

  Array1,array2,array3, ...    为 2 到 30 个数组，其相应元素需要进行相乘并求和。

特点：

---

• 支持数组建运算

• 将非数值型元素作为0处理：单元格中有字符不影响计算结果

• 数值必须有相同的高度，否则返回错误值：A栏为A3:A20，B、C、D等需要计算的都必须为3:20，否则返回错误值VALUE!。

实例：

---

SUMPRODUCT解决四大类统计问题

一、用SUMPRODUCT进行求和

• 替代SUM求和

• 单条件求和（替代SUMIF）

• 多条件求和（替代SUMIFS）

代替SUM求和：

---

如图

• 需统计C列领取文具合计数量、及领取文具总和

  C列领取文具合计数量SUM（C2:C8）；

  及领取文具总和=C2*D2+C3*D3+……+C8*D8
  

• SUMPRODUCT函数公式
  

如图

合计C列数量：

【SUM公式】=SUM（C2:C43)，计算不了，C6为文本。

【SUMPRODUCT】H4=SUMPRODUCT（C2:C8)

解释：当只有一个数组时，SUMPRODUCT函数作为合计数值使用；忽视C6文本，自动计算为0

计算领取文具金额之和：如只有C列与D列时，需要计算领取玩具合计总和。

传统SUM法：

传统方法根本计算不了，只能加上E列作为辅助列，再使用SUM函数计算

H5=SUM（E2:E43），而且由于C列出现字符，SUM无法计算，必须把字符删除才可。

SUMPRODUCT法：

• 忽略字符

• 不需辅助列

• H4=SUMPRODUCT（C2:C8，D2:D8)

代替SUMIF单条件：

---

如图，需统计办公室人员1月薪酬

SUMIF计算：L4=SUMIF($F:$F,$K4,H:H)

SUMPRODUCT语法：

L4为例：办公室人员的薪酬。

L4 = SUMPRODUCT((F2:F10=K5)*(H2:H10)) 

---

                                 （条件列=条件）* （计算区域）

两个函数使用注意事项：SUMIF可以使用列为计算、条件列，但 SUMPRODUCT只能使用区域进行计算。

设置如图：

代替SUMIFS多条件：

---

如图，需统计办公室女性人员1月薪酬

SUMIF计算：C14=SUMIFS(H2:H10,F2:F10,A14,C2:C10,A15)

SUMPRODUCT语法：

C14为例：办公室女性人员的薪酬。

C14=SUMPRODUCT((F2:F10=A14)*(C2:C10=A15)*(H2:H10))

简单来说，多条件的合计就是  不同条件*计算区域，比SUMIFS简单很多。

---

设置如图：

二、计数

• 单条件计数（替代COUNTIF）

• 多条件计数（替代COUNTIFS）

代替COUNTIF计数：

---

如图，需要计算不同部门人数

方法：

COUNTIF：C14=COUNTIF(F2:F10,A14)

                                         （计数区域，条件）

SUMPRODUCT：C14=SUMPRODUCT(N(F2:F10=A14))

• 说明：

• 1.公式中用到了Excel中最短函数N，其主要作用为将非数值的值转换为数值。

• 2.首先判断F2:F10=A14是否成立，如果成立，返回True，否则返回False，然后用N函数将对应的值转换为1和0。

• 3.当Sumproduct函数只有一个数据区域时，对区域中的值进行求和处理。以“办公室”为例：判断和转换后的结果为{0;0;1;0;0;1;0;0;1},求和的结果为3。达到了计数目的。

【GIF演示如下】

代替COUNTIFS计数：

---

需要计算办公室、薪酬大约3000元人数

SUMPRODUCT：

C14=SUMPRODUCT(N(F2:F10=A14)*(H2:H10>=3000))

设置如图

三、多权重合计

多权重合计，合计数据由不同列，不同比例合计而成

【GIF演示如下】

• 方法：
  

SUMPRODUCT：E3=SUMPRODUCT(B$2:D$2,B3:D3)

• 说明：

• 1.从表中我们可以看出要员工最终评分，其中个人自评占10%，互评占30%，领导评价占60%。

• 2.B$2:D$2各项所占的比是固定不变的，所以行绝对引用，不可以随着单元格的变动而变动，而每个人的成绩要随着人员的变化而变化，所以才用相对引用的形式。

四、中国式排名中SUMPRODUCT的应用

如图：需要对销售业绩进行排名

中国式排名特点：出现并列名次。中国式排名次序是：1,2,2,3。也就是并列第二名后是第三名，不会漏掉名次的数字

SUMPRODUCT公式：

E3=SUMPRODUCT((C$3:C$7>C3)*1/(COUNTIF(C$3:C$7,C$3:C$7))))+1

公式说明：

1.首先判断C$3:C$7>C3>F7条件是否成立，如果成立，则返回True，=1，否则返回False，=0。另外*1可以删除。

2.(COUNTIF(C$3:C$7,C$3:C$7)的作用就是形成一个5个元素为1的数组。

3.第一步和第二步对应的数组元素进行除法运算，形成一个新的数组，而Sumproduct函数当数组区域为单一区域时，对区域中的值进行求和运算。

4.公式中的1为附加值。

.

---

花一秒钟，扫一扫收藏