[练习]多条件求和及sumproduct函数

[练习]多条件求和及sumproduct函数Excel多条件求和 & SUMPRODUCT函数用法详解

龙逸凡

日常工作中，我们经常要用到多条件求和，方法有多种，第一类:使用基本功能来实现。主要有:筛选、分类汇总、数据透视表、多条件求和向导;第二类:使用公式来实现方法。主要有:使用SUM函数编写的数组公式、联用SUMIF和辅助列(将多条件变为单条件)、使用SUMPRODUCT函数、使用SUMIFS函数(限于Excel2007及以上的版本)，方法千差万别、效果各有千秋。本人更喜欢用SUMPRODUCT函数。由于Excel帮助对SUMPRODUCT函数的解释太简短了，与SUMPRODUCT函数的作用相比实在不匹配，为了更好地掌握该函数，特将其整理如下。

龙逸凡注:欢迎转贴～但请注明作者及出处。

一、基本用法

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

语法:

SUMPRODUCT(array1,array2,array3, ...)

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

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

A B C D

1 Array 1 Array 1 Array

2 Array 2

2 3 4 2 7

3 8 6 6 7

4 1 9

5 3

公式解释:两个数组的所有元素对应相乘，然后把乘积相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。计算结果为156

二、扩展用法

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列为工资)

三、注意事项

1、数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误

值 #VALUE!。

2、SUMPRODUCT函数将非数值型的数组元素作为 0 处理。

3、在SUMPRODUCT中，2003及以下版本不支持整列(行)引用，必须指

明范围，不可在SUMPRODUCT函数使用A:A、B:B，Excel2007及以上版本可以整列(列)引用，但并不建议如此使用，公式计算速度慢。

4、SUMPRODUCT函数不支持“*”和“,”通配符

SUMPRODUCT函数不能象SUMIF、COUNTIF等函数一样使用“*”和“,”等通配符，要实现此功能可以用变通的方法，如使用LEFT、RIGHT、ISNUMBER(FIND())或ISNUMBER(SEARCH())等函数来实现通配符的功能。如:

=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*(LEFT(D2:D10,1)="

龙")*C2:C10)

=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*((ISNUMBER(FIND("龙逸凡",D2:D10)))*C2:C10))

注:以上公式假设D列为职工姓名。ISNUMBER(FIND())、ISNUMBER(SEARCH())作用是实现“*”的通配功能，只是前者区分大小写，后者不区分大小写。

5、SUMPRODUCT函数多条件求和时使用“，”和“*”的区别:当拟求和的区域中无文本时两者无区别，当有文本时，使用“*”时会出错，返回错误值

#VALUE!，而使用“，”时SUMPRODUCT函数会将非数值型的数组元素作为 0 处理，故不会报错。也就是说:

公式1:=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*C2:C10)

公式2:=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")，C2:C10)

当C2:C10中全为数值时，两者计算结果一样，当C2:C10中有文本时公式1会返回错误值 #VALUE!，而公式2会返回忽略文本以后的结果。

四、网友们的精彩实例

1、求指定区域的奇数列的数值之和

=SUMPRODUCT(MOD(COLUMN(A1:F1),2)*A1:F1)

2、求指定区域的偶数行的数值之和

=SUMPRODUCT(((MOD(ROW(A1:A22),2))-1)*A1:A22)*(-1)

3、求指定行中列号能被4整除的列的数值之和

=SUMPRODUCT((MOD(COLUMN(A1:P1),4)=0)*A1:P1)

4、.求某数值列前三名分数之和

,SUMPRODUCT(LARGE(B1:B16,ROW(1:3)))

5、统计指定区域不重复记录的个数

,SUMPRODUCT(1/COUNTIF(V11:V15,V11:V15))

相关链接:

1、逸凡工作簿合并助手,excel表格合并不用愁，免费

2、逸凡对账能手V1.0正式版，免费且代码公开

3、龙逸凡Excel培训手册之潜龙在渊，免费

4、《龙逸凡Excel培训手册》之飞龙在天，免费

5、逸凡账务系统V3.0正式版，永久免费使用

6、逸凡账务系统V4.0，小企业、兼职代账专用，收费

=sum(1/countif(a1:b5,a1:b5))为什么计算的是不重复值, COUNTIF 函数对区域中满足单个指定条件的单元格进行计数。语法COUNTIF(range, criteria) COUNTIF 函数语法具有下列参数 (参数:为操作、事件、方法、属性、函数或过程提供信息的值。):

, range 必需。要对其进行计数的一个或多个单元格，其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。

, criteria 必需。用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。例如，条件可以表示为 32、">32" 、B4、"苹果" 或"32"。