关于SUMPRODUCT函数用法的问题

admin管理员组

文章数量:1530844

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

关于SUMPRODUCT函数用法的问题

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)

上述解释应该是对SUMPRODUCT用法的比较准确的解释.但本人有不理解之处,看下面的公式:

=SUMPRODUCT(($B2:$B26=F1)*($C2:$C26>=20)*($C2:$C26<30)) 见附件

这个公式中的第一部分($B2:$B26=F1)是数组吗?这个公式中的数组元素是怎么对应相乘而后求和的??

希望高手帮我解释一下.

我找到了一个比较详细的解释.

=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。

=SUMPRODUCT(($B2:$B26=F1)*($C2:$C26>=20)*($C2:$C26<30))

简单的说，sumproduct带有数组功能，不需要三键结束。

这个公式可以理解为

($B2:$B26=F1)和($C2:$C26>=20)和($C2:$C26<30)

TOP

三个条件均为真的时候统计为1，那么B2:B26和C2:C26同时满足这三个条件的有多少个，即返回多少个1，。。。

sumif,countif用法,SUMPRODUCT 函数条件统计_我的编程空间

excel学习中 2009-11-01 13:39:02 阅读358 评论0 字号：大中小

sumif,countif用法,SUMPRODUCT 函数条件统计

2008-11-03 16:00

SUMIF

根据指定条件对若干单元格求和。

语法

SUMIF(range,criteria,sum_range)

Range 为用于条件判断的单元格区域。

Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、">32" 或 "apples"。

Sum_range 是需要求和的实际单元格。

说明

只有在区域中相应的单元格符合条件的情况下，sum_range 中的单元格才求和。

如果忽略了 sum_range，则对区域中的单元格求和。

Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用 COUNTIF 函数。如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额返回销售红利），则可使用 IF 函数。

示例

如果您将示例复制到空白工作表中，可能会更易于理解该示例。

操作方法

创建空白工作簿或工作表。

请在“帮助”主题中选取示例。不要选取行或列标题。

从帮助中选取示例。

按 Ctrl+C。

在工作表中，选中单元格 A1，再按 Ctrl+V。

若要在查看结果和查看返回结果的公式之间切换，请按 Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。

A B

属性值 佣金

100,000 7,000

200,000 14,000

300,000 21,000

400,000 28,000

公式 说明（结果）

=SUMIF(A2:A5,">160000",B2:B5) 属性值超过 160,000 的佣金的和 (63,000)

即是a2:a5中大于160000 统计b2:b5 中符合条件的合计数

//////////////////////////////////////////////////

countif 函数是计算区域中满足给定条件的单元格的个数。

A B

1 数据 数据

2 苹果 32

3 柑桔 54

4 桃 75

5 苹果 86

公式 说明 （结果）

=COUNTIF(A2:A5,"apples") 计算第一列中苹果所在单元格的个数 (2)

=COUNTIF(B2:B5,">55") 计算第二列中值大于 55 的单元格个数 (2)

////////////////////////////////

基本用法是求两列及两列以上数据的乘积之和。

如：=SUMPRODUCT(A1:A20,B1:B20)

表示A1*B1+A2*B2+A3*B3+……A20*B20

其他的用法如你说的例子是统计，还可以进行条件求和。

如：=SUMPRODUCT((A1:A20="女")*(B1:B20=25)*C1:C20))

表示A列为女并且B列是25的C列的数据之和。

Excel 中SumProduct函数的妙用！

By 平心桨 发表于 2008-11-20 11:12:00

Excel的SUMPRODUCT函数提供Excel数组公式的大多数功能，并且在使用上不复杂。

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

SUMPRODUCT(array1,array2,array3, „)

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

另一种用法：

Sumproduct((A1:A100=数值1)*(B1:B100>数值2),C1:C100)

用于统计第一行到第100行中，A列等于数值1，B列大于数值2时，C列的数值和

这个功能很爽„„

另，Sumproduct((A1:A100=数值1)*(B1:B100>数值2)*(C1:C100<数值3))表示统计第1行到第100行中，A列等于数值1，B列大于数值2，C列小于数值3的记录的数量。

很cool的一个函数！

Excel里面sumproduct函数的完整用法？“--”是什么意思？

以下几个写法有什么区别，搞不清楚--和*得区别：

（1）=sumproduct((A1:A100="男")*(B1:B100="本科"))；

（2）=sumproduct((A1:A100="男")，(B1:B100="本科"))；

（3）=sumproduct(--(A1:A100="男"),--(B1:B100="本科"))；

（4）=sumproduct(--(A1:A100="男")*--(B1:B100="本科"))；

;计算男性本科学历的人数

到底哪个是正确的公式，--到底是什么含义，网上似乎没人说这个符号。

另外，是不是=sumproduct(条件1*条件2)用来求符合条件的个数，

而=sumproduct(条件1*条件2，区域3)用来求区域3里面同时符合条件1、2的数值的和？？？？？？？

回复Excel里面sumproduct函数的完整用法？“--”是什么意思？

--excel里叫做减负运算，其目的是将字符串格式的数字转变成真正意义上的数字，重而参加预算。你可以将它理解为两个减号，结果是负负得正，没有改变原数据的正负，但将其变成了数字。

你可以先将某个空单元格例如a1设为文本，里面输入12345，在b1里输入=sum(a1)，b2里输入=sum(--a1)比较一下就能看出分别了

支持楼主发的学习资源，点击下面“我要评分”按钮为他评分吧！同时你也可以点击“我顶”按钮支持此主题。⌒＿⌒

[函数公式] SUMPRODUCT函数的另一妙用(多条件汇总)

SUMPRODUCT, 函数, 妙用, 条件, 汇总

Excel的SUMPRODUCT函数基本功能是可以方便地计算工作表内多列中对应值相乘后的和，其语法为：SUMPRODUCT(array1,array2,array3, …)

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

下面通过示例介绍SUMPRODUCT函数的另一个妙用多条件求和，如下图1所示的工作表：要统计姓名=“张三”，并公司=“A”，用工数的总计。

其中所定义的名称为：

名称 引用范围

公司 =Sheet1!$D$3:$D$17

全部数据=Sheet1!$A$2:$E$17

日期 =Sheet1!$A$3:$A$17

姓名 =Sheet1!$B$3:$B$17

性别 =Sheet1!$C$3:$C$17

用工数 =Sheet1!$E$3:$E$17

(1)要计算工作表中姓名是张三且公司为A的用工数统计，则可以使用下面的公式：

=SUMPRODUCT((Sheet1!$B$3:$B$17=”张三”),(Sheet1!$D$3:$D$17=”A”),（Sheet1!$E$3:$E$17)）

返回结果24。

(2)要获取姓名为张三且公司为A的总数，则可以使用下面的公式：

=SUMPRODUCT((Sheet1!$B$3:$B$17=”张三”)*(Sheet1!$D$3:$D$17=”A”)*1)

或=SUMPRODUCT((Sheet1!$B$3:$B$17=”张三”)*(Sheet1!$D$3:$D$17=”A”))

结果为4。

注意:公式中每一个条件查询的范围必须一样，如果是二个以上的条件：例=SUMPRODUCT((Sheet1!$B$3:$B$17=”张三”),(Sheet1!$D$3:$D$17=”A”),（其他条件....）,（Sheet1!$E$3:$E$17)）

应约上帖：关于用SUMPRODUCT取代SUM数组公式的想法

大家用过数组公式的都可能知道，对于多条件求和或计数，我们一般都采用这种数组公式来计算。如：

对于1个明细数据(见例)，我们需要求该班男、女生各科成绩大于各科平均分的人数，这个问题不难，大家一般都会用sum()来多条件求值就行了(注：数组公式)：

=SUM(($C$3:$C$428=$K3)*(D$3:D$428>=AVERAGE(D$3:D$428)))

确实这是解决问题的一种方法，但往往对于初用数组公式的朋友常常会遗忘(或不知道)用(Ctrl+shift+Enter)组合键来结束编辑(论坛中常常有类似的提问)。

出于这个原因，我想到能否用SUMPRODUCT()函数来代替常用SUM()多条件求和公式呢？结果答案是肯定的。

把上面的公式稍做修改，请见：

=SUMProduct(($C$3:$C$428=$K3)*1,(D$3:D$428>=AVERAGE(D$3:D$428))*1)

而且直接用Enter来结束，函数仍然可以正确计算结果。

原因在于：SUMPRODUCT()函数本身就支持数组间运算(相同维数的数组相乘后再加总)，因此我们毋须用数组公式组合键来结束。

另：提醒大家一点，如果大家试图将公式改为：

=SUMProduct(($C$3:$C$428=$K3),(D$3:D$428>=AVERAGE(D$3:D$428)))

公式不能得出正确结果。

如果多个条件这样并列写入，系统默认是用AND关系(即相乘)来运算，但是SUMPRODUCT不支持逻辑值数组常量间相乘(也算是小遗憾吧：))，因此请一定要将逻辑值转换成数值才行。

大家可能觉得麻烦，因此我建议使用缩写公式，实际上是将多个数组参数变为一个数组，也就可以避免不同维数数组间相乘而带来的错误麻烦了。

=SUMProduct(($C$3:$C$428=$K3)*(D$3:D$428>=AVERAGE(D$3:D$428)))

同理：我们如果需要求出所有男生中总分大于平均总分的所有总分，我们也可以用公式：

=SUMPRODUCT(($C$3:$C$428=$K13)*(I$3:I$428>=AVERAGE(I$3:I$428)),I$3:I$428)

如果数组的维数相同，建议使用这种方法，如果直接相乘出现错误，可能是I列出现非数值字符，用逗号分隔后，系统可以自动忽略文本的。

对于条件求和的应用，大家可以结合下面的实例来理解(但对于使用该函数，运算速度是否有提高，还待各位朋友验证)，希望对大家有帮助，谢谢!

最后再提醒大家一点：如果数组的维数相同，建议用逗号分隔，系统会自动忽略文本的，而且并不是所有用SUM()＋数组公式（如公式内部加入IF来判断的）都可以用SUMPRODUCT()函数来代替的，该帖只是告诉大家另一种思路或另一种想法

不过，有几个提法不是很认同，SUM(IF()*())的方式，也可以用Sumproduct(if()*())的方式来替代。

你提到的数组维数相同，这个说法有误，应该是数组尺寸相同，数组的维数是指一维，二维，三维数组等，Excel函数只能处理1维和2维数组，Vba可以处理三维。而尺寸是指数组的大小，即行列数。

举例，=SUMPRODUCT(A1:B4,C1:D6)，两个参数都是二维的，但不同行数就不行了

这样的例子很多的,可以参照excel的帮助文档:

全部显示

OFFSET

请参阅

以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。

语法

OFFSET(reference,rows,cols,height,width)

Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。

Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。

Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。

Height 高度，即所要返回的引用区域的行数。Height 必须为正数。

Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。

说明

如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值 #REF!。

如果省略 height 或 width，则假设其高度或宽度与 reference 相同。

函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数

OFFSET 可用于任何需要将引用作为参数的函数。例如，公式 SUM(OFFSET(C2,1,2,3,1)) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。

示例

如果您将示例复制到空白工作表中，可能会更易于理解该示例。

操作方法

创建空白工作簿或工作表。

请在“帮助”主题中选取示例。不要选取行或列标题。

从帮助中选取示例。

按 Ctrl+C。

在工作表中，选中单元格 A1，再按 Ctrl+V。

若要在查看结果和查看返回结果的公式之间切换，请按 Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。

1

2

3

4

A B

公式 说明（结果）

=OFFSET(C3,2,3,1,1) 显示单元格 F5 中的值 (0)

=SUM(OFFSET(C3:E5,-1,0,3,3)) 对数据区域 C2:E4 求和 (0)

=OFFSET(C3:E5,0,-3,3,3) 返回错误值 #REF!，因为引用区域不在工作表中

SUMPRODUCT函数基础

2009年09月28日, 12:53 下午

(7 人投票, 平均: 4.43 out of 5)

Loading ...

SUMPRODUCT函数是一个数组类型的函数，能够计算多个区域的数值相乘后之和。其语法为：

SUMPRODUCT(数组1,数组2,数组3,„„)

返回数组（区域）中对应项相乘后的和。

例如下图所示的工作表数据：

要求将列A和列B中同一行的数据相乘后的结果相加，即：

A1×B1+ A2×B2+ A3×B3+„+ A10×B10

可使用如单元格C3中的公式：

=SUMPRODUCT(A1:A10,B1:B10)

如果将单元格区域A1：A10命名为“DataA”，将单元格区域B1：B10命名为“DataB”，那么可以使用公式：

=SUMPRODUCT(DataA,DataB)

说明：





在SUMPRODUCT函数的参数中，数组的大小必须相等，否则将返回#NUM!错误。

SUMPRODUCT函数将数组中不是数字的数组元素作为0对待。

（应用）基于两个条件汇总数据

例如，下表所示的数据：

现在要求华东区域产品B的销售量，则可使用下面的公式：

=SUMPRODUCT((C2:C10)*(A2:A10=”华东”)*(B2:B10=”B”))

结果为100。

其中：









探讨

有时，如果不合适地应用SUMPRODUCT函数，将会得不到正确的结果。例如，下表所示数据：

C2：C10为包含销售量的单元格区域

通过A2：A10=”华东”产生一个数值为1，0的数组，即如果列A中的数据为华东则值为1，否则为0

同理，通过B2:B10=”B”产生一个数值为1，0的数组

SUMPRODUCT函数将这些数组分别相乘后相加即得到结果

现在要求华中区域与目标日时间差为负数的项目的销售额。

其中，将单元格区域“A2:A10”命名为“区域”，将单元格区域“B2:B10”命名为“销售额”，将单元格区域“C2:C10”命名为“时差”。

如果我们输入下面的公式：

=SUMPRODUCT((时差<0),(区域="华中"),销售额)

将得不到正确的结果。由于某种原因，SUMPRODUCT函数不能正确处理布尔值，因此公式无效。

下面的公式在SUMPRODUCT函数中将布尔值乘以1，将得到正确的结果：

=SUMPRODUCT(1*(时差<0),1*(区域="华中"),销售额)

也可以像上例一样，使用下面的公式：

=SUMPRODUCT(销售额*(区域=”华中”)*(时差<0))

本文标签： 数组公式函数区域