SUMPRODUCT函数的应用:条件计数、求和和排序功能的使用介绍

admin管理员组

文章数量:1530846

2023年12月19日发(作者：)

SUMPRODUCT函数可以方便地计算工作表内多列中对应值相乘后的和，其语法为：

SUMPRODUCT(array1,array2,array3, …)

其中，Array1, array2, array3, … 为 2 到 30 个数组，其相应元素需要进行相乘并求和。数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 该函数将非数值型的数组元素作为 0 处理。

用法一：两个数组的所有元素对应相乘

看一个例子就容易明白SUMPRODUCT的用法：

A B C D （列号）

1 数组1 数组1 数组2 数组2 （第1行）

2 1 2 10 20 （第2行）

3 3 4 30 40 （第3行）

4 5 6 50 60 （第4行）

公式： =SUMPRODUCT(A2:B4, C2:D4)

说明：两个数组的所有元素对应相乘，然后把乘积相加，即 1*10 + 2*20 + 3*30 + 4*40 + 5*50 +

6*60(结果为910)

用法二：多条件求和+求个数

1、使用SUMPRODUCT进行多条件计数 语法：＝SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n))

作用：统计同时满足条件1、条件2到条件n的记录的个数。实例：=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称"))公式解释：统计性别为男性且职称为中级职称的职工的人数

2、 使用SUMPRODUCT进行多条件求和 语法：＝SUMPRODUCT((条件1)*(条件2)* (条件3) *…(条件n)*某区域) 作用： 汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额。实例： =SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*C2:C10) 公式解释：统计性别为男性且职称为中级职称的职工的工资总和（假设C列为工资）

SUMPRODUCT(条件1*条件2*条件3...条件N)利用此函数进行多条件计数时，* ：满足所有条件；SUMPRODUCT(条件1+条件2+条件3...+条件N) + ：满足任一条件

我找到了一个比较详细的解释=SUMPRODUCT(($B2:$B26=F1)*($C2:$C26>=20)*($C2:$C26<30))

=SUMPRODUCT((G1:G3="男")*(E1:E3<=60))

这个公式的意思是统计，G1－G3是男的，同时E1－E3数值小于等于60的人数。

首先这是一个数组公式，要按Ctrl+Shift+Enter结束。

然后看他的计算过程：

假如G1＝男，G2，G3都为女，然后E1＝65，E2＝60，E3＝80。

这时候公式变为

=SUMPRODUCT((TRUE，FALSE，FALSE)*(TRUE，FALSE，FALSE))

这不知道能理解不，因为G1=男，所以第一个值为TRUE。第二个不为男，值就为FALSE。

接下来，TRUE和FALSE分别代表1和0。所以公式变为：

=SUMPRODUCT((1，0，0)*(1，0，0))

然后接下来就是SUMPRODUCT的计算过程了

=1*1+0*0+0*0=1

所以最后的结果等于1。

1、多条件计算人数=SUMPRODUCT((I241:I250="是")*1)

2、求指定年龄、性别人数=SUMPRODUCT((H252:H260="男")*1,(I252:I260>25)*1)

3、汇总一班人员获奖次数=SUMPRODUCT((H265:H274="一班")*I265:I274)

4、汇总一车间男性参保人数=SUMPRODUCT((G276:G284&H276:H284&I276:I284="一车间男是")*1)

5、汇总所有车间人员工资=SUMPRODUCT(--NOT(ISERROR(FIND("车间",G286:G294))),I286:I294)

6、汇总业务员业绩=SUMPRODUCT((H296:H305={"江西","广东"})*(I296:I305="男")*J296:J305)

使用注意：1、本例公式也可以不使用数组，改用"+"连接两个条件，公式如下:=SUMPRODUCT(((H296:H305="江西")+(H296:H305="广

东"))*(I296:I305="男")*J296:J305)。2、公式中“+”连接的条件表示满足任意条件就求和，而“*”连接的条件则表示同时满足所有条件才求和。

1、计算男性人数：=SUMPRODUCT((B2:B13="男")*1)

2、多条件求和，求男性及格人数：=SUMPRODUCT((C2:C13>=60)*1,(B2:B13="男")*1)

3、汇总编号第一个字符为A的成绩总数=SUMPRODUCT((A2:A13="A*")*C2:C13)

注意：也可以将两个数组分成两个参数，但是第一参数需要利用*1或者其他方式将逻辑值转换成数值=SUMPRODUCT((B2:B13="男")*1,C2:C13)

4、多条件求和，汇总三班籍贯为浙男性人数：=SUMPRODUCT((B2:B13&D2:D13:C2:C13="男三班>=60")*1)

注意：本条也可用如下方式实现=SUMPRODUCT((B2:B13="男")*1,(D2:D13="三班")*1,(E2:E13="浙")*1)

5、汇总所有编号包含A的学生成绩：=SUMPRODUCT(--NOT(ISERROR(FIND("A",A2:A13))),C2:C13)

注意：SUMPRODUCT函数不支持通配符。

说明：FIND函数在A2:A13区间查找包含A的编号，如果找到则运算结果为一个数值标识该单元格的位置，如果找不到将长生一个错误值，再使用NOT(ISERROR())来判断哪些单元格包含A，得到一个由TRUE和FALSE组成的数组，再用--将这组逻辑值转换成数值，最后与C2:C13相乘得出汇总值。

本例中按类别统计了销售记录表，此时需要统计出女式连衣裙和女式职业装两类的销售金额，我们可以直接使用sumproduct函数来实现。如下图所示。

选中E8单元格，输入公式：=SUMPRODUCT(((B2:B19="女式连衣裙")+(B2:B19="女式职业装")),$C$2:$C$19)

用法三：实现有条件排名

全市三所学校各个专业的学生成绩都放到了一个工作表中，格式如图1所示。为了做好成绩分析，主任要求做好两个排名：一是排出每位学生在全市相同专业的学生中的名次；二是排出每位学生在本校本专业中的名次；两个排名都以总分为依据。

图1(点击看大图)

使用了SUMPRODUCT函数来完成这个有条件的排名工作。具体实现过程如下：

一、准备工作 选定总分所在的H2:H1032单元格区域，点击功能区“公式”选项卡“定义的名称”功能组中“定义名称”按钮，在弹出的“新建名称”对话框“名称”输入框中输入为此区域定义的名称

“zongfen”。此时，对话框下方的“引用位置”后的输入框中已经自动输入我们选定的单元格区域“=对口!$H$2:$H$1032”，如图2所示。

图2 按同样的方法，选定学校所在单元格区域I2:I1032、专业所在单元格区域J2:J1032，分别为它们指定名称“xuexiao”和“zhuanye”。 完成后，这准备工作就算是结束了。

二、排定名次

在K1单元格输入标题“按专业排名”。点击K2单元格，输入公式“=SUMPRODUCT((zhuanye=$J2)*($H2

在L1单元格输入标题“校内专业排名”。点击L2单元格，输入公式“=SUMPRODUCT((zhuanye=$J2)*($H2

图3(点击看大图)

本文标签： 条件汇总数组排名公式