4.1 求和类函数

在前面的章节中，我们已讲解过SUM函数的应用，接下来再学习SUMPRODUCT、SUMIF、SUMIFS这三个关于求和的函数，这些函数将帮助用户解决各种各样关于求和的问题。

4.1.1 计算乘积之和函数：SUMPRODUCT

SUMPRODUCT函数是在给定的几组数组或引用区域中，将数组或引用区域间对应的元素相乘，并返回乘积之和，也就是先相乘再相加。

函数语法说明：

SUMPRODUCT(array1,[array2],[array3],...[array255])

● array1：必需参数。其相应元素需要进行相乘并求和的第一个数组参数。

● array2，array3，...array255：可选参数。从2 到 255 个数组参数，其相应元素需要进行相乘并求和。

注意两个问题：一是多个数组之间的元素个数必须相同，否则，将返回错误值；二是函数 SUMPRODUCT将非数值型的数组元素作为0处理。

比如，=SUMPRODUCT({20;30;40;50},{1;3;5;7})，运算规则是20*1+20*3+40*5+50*7，运算结果为660。

实例36 计算各产品的总金额

问题描述： 如图4-1所示，先将表中的单价乘以数量，再将所得到的金额相加，就计算出了总金额。如果遇到“未定”，则处理成0。

解决方法： 将“单价”列的数据作为SUMPRODUCT函数的第1参数，将“数量”列的数据作为第2参数。如果区域中有文本，则会自动处理成0。

操作步骤： 如图4-2所示，首先将鼠标光标定位在E2单元格，输入公式=SUMPRODUCT (B2:B7,C2:C7)，然后按Enter键完成计算。

步骤1： 计算SUMPRODUCT（B2:B7,C2:C7）部分。由于SUMPRODUCT函数能将对应的数组或区域中不是数字（错误值除外）的值识别成0，相当于SUMPRODUCT（{2.5;3;1.5;0;3.2;4},{100;0;50;80;120;60}），对应计算的方式为{2.5*100;3*0;1.5*50;0*80;3.2*120;4*60}，计算结果为{250;0;75;0;384;240}。

步骤2： 使用SUM函数对步骤1的结果进行求和，公式为SUMPRODUCT ({250;0;75;0;384;240}),计算结果为949。

注意 由于 SUMPRODUCT 函数本身就是按数组的方式运算的，所以输入此公式后不用按Ctrl+Shift+Enter组合键。

另外，也可以使用=SUM(IFERROR(B2:B7*C2:C7,0))公式，按Ctrl+Shift+Enter组合键，完成计算。此公式的思路是直接将“单价”列乘以“数量”列，遇到有文本时，会出现错误。所以还要用IFERROR函数来处理，当有错误值时返回0，最后用SUM函数进行求和。

4.1.2 单条件求和函数：SUMIF

SUMIF 函数，是对有条件的单元格区域求和，即函数对单元格区域中符合指定条件的值求和。虽然SUM函数也可以进行条件求和，但从运算速度和灵活程度上来说，SUMIF函数更方便一些。

函数语法说明：

SUMIF(range,criteria,[sum_range])

● range：必需参数。要进行条件判断的引用区域，请注意此参数一定是引用区域，不能为数组。

● criteria：必需参数。要进行判断条件，形式可以为数字、表达式、单元格引用、文本等。

● sum range：可选参数。要进行实际求和的单元格区域。如果省略此参数不写，则将第1参数中的单元格区域求和。此参数也是引用区域，不能为数组。

实例37 求各种花卉的销售总数量

问题描述： 图4-3左边的“花卉销售表”中记录了每种花的销售数量，图4-3右边的“花卉销售汇总表”提供了需要汇总的花名，在F列区域中求出每种花的销售总数量。

解决方法： 可以按照数组思路，将需要求和的花名筛选出来，再乘以数量，就筛选出了要对应求和的数量。也可以直接使用SUMIF函数来达到这种效果。

操作步骤： 如图4-4所示，首先将鼠标光标定位在F3单元格，输入公式=SUMIF(A$3:A$8,E3,C$3:C$8)，再将公式向下填充，完成求和计算。

步骤1： 以E3单元格中的“百合”为条件，在条件区域A$3:A$8中判断是否有等于“百合”的单元格，如果有，则将C$3:C$8区域中对应的单元格求和。

步骤2： 通过步骤1的分析发现，A3和A6单元格是等于“百合”的，所以将对应的C3和C6单元格中的数量进行求和。

另外，也可以用数组公式=SUM((A$3:A$8=E3)*C$3:C$8)来完成，但运算速度上没有SUMIF函数快。

实例38 求花卉的销售数量大于或等于700件的总数量（SUMIF函数忽略第3参数的应用）

问题描述： 求图4-5中“花卉销售表”中销售数量大于或等于700的花卉品种的数量和，将结果写入E3单元格。

解决方法： 直接用SUMIF函数进行判断并求和。

操作步骤： 如图4-6所示，首先将鼠标光标定位在E3单元格，输入公式=SUMIF（C3：C8，〝＞=700〝），然后按Enter键。

步骤： 条件判断区域是C3:C8，符合大于或等于700的条件的有980、800、780三个值。由于第3参数忽略不编写，则将第1参数中符合条件的三个值进行求和。

另外，也可以使用数组公式=SUM((C3:C8>=700)*C3:C8)来完成。

在SUMIF函数的第2参数criteria中，也可以使用通配符做条件。通配符是一种通用的匹配符号。表4-1中列举了通配符的注释和相关示例，读者可以体会一下通配符的编写规则。

实例39 求竞赛中初中和高中最高分的总和分别是多少（SUMIF函数第2参数通配符的应用）

问题描述： 如图4-7所示，“某竞赛初中、高中最高分”表中列出了相关数据。现在要统计出初中和高中最高分的总分分别是多少？也就是将初中三个年级的最高分的分数相加，高中三个年级的最高分的分数相加。

解决方法： 本实例可以在SUMIF函数的第2参数中使用通配符来完成。此实例的条件是求初中、高中的总分，说明有两个条件，并且两个条件是各自独立没有关联的，那么多个条件可以用{〝初？〝；〝高？〝}数组形式来放置。

来看看初中的条件写法，因为初一、初二、初三年级都是以初字开始的，并且只有两个字，所以用〝初？〝。如果字符长度不定，则可以使用〝初*〝，高中的条件写法与此相同。

因为我们预测到有两个结果。所以输入公式时，要选择F3:F4单元格区域，并且要注意因为选择单元格是纵向的，所以条件之间用分号（;）分隔。如果选择的是横向两个单元格来放置结果，则用逗号（,）进行分隔。

操作步骤： 如图4-8所示，首先选择F3：F4单元格区域，然后将鼠标光标定位到编辑栏，输入数组公式=SUMIF（B3：B8，{〝初？〝；〝高？〝}，C3：C8），再按Ctrl+Shift+Enter组合键，完成计算。

步骤1： SUMIF函数第1参数中的B3：B8区域，是要进行条件判断的区域；第2参数的条件为{〝初？〝；〝高？〝}，表示以“初”字开始，长度为2，以及以“高”字开始，长度为2；第3参数中的C3：C8区域是实际要求和的区域。

步骤2： 公式SUMIF（B3：B8，{〝初？〝；〝高？〝}，C3：C8）的计算结果为{293；294}。

下面讨论一下SUMIF函数的运算机制，在编写SUMIF函数时，第1参数的判断区域和第3参数的求和区域的尺寸大小是一样的，然后将两个区域的单元格一一对应判断进行求和。但如果尺寸大小不一样，则只要确定第1参数的判断区域和第3参数的求和区域即可（只指定对应左上角的起点单元格也可以）。如图 4-9 所示，标准写法是=SUMIF（A1：C5，〝A〝，E1：G5），但也可以写成=SUMIF（A1：C5，〝A〝，E1），SUMIF 函数会对第 1 参数中的单元格区域进行逐个判断，如果条件等于“A”，则对右侧对应的单元格值进行求和。有时并不会像图4-9中那样第1参数（判断区域）和第3参数（求和区域）呈现得那么清楚，有可能第1和第3参数的区域有交叉重叠。但无论区域怎样变化，运算规则是不会变化的。

实例40 求某竞赛中初中和高中最高分的总和分别是多少（数据结构有变化）

问题描述： 图4-10所示的表中，“某竞赛初高中前三名分数表”中列出了初中、高中学生的最高分数，现在要分别计算出初中、高中的最高分总和。

解决思路： 可以利用SUMIF函数的运算机制来解决本实例，可能有人觉得直接用两次SUM函数来求和就可以搞定，但如果初中、高中的数据混合显示就不行了，所以我们使用SUMIF函数更为适合。

操作步骤： 如图4-11所示，首先选择I3：I4单元格区域，然后将鼠标光标定位到编辑栏，再输入数组公式=SUMIF（B3：E5，{〝初？〝；〝高？〝}，C3：F5），也可以是=SUMIF（B3：E5，{〝初？〝；〝高？〝}，C3），按Ctrl+Shift+Enter组合键，完成计算。

步骤1： 如图4-12所示，将SUMIF函数中的第1和第3参数区域拆解到两个独立的区域，更方便做解析。计算数组条件中的第1个条件〝初？〝，当判断区域中的值为初一、初二、初三时，条件均成立。那么将其对应单元格中的98、96、99求和，得到293。除此之外的条件区域中的其他单元格均不成立。

步骤2 ：如图4-13所示，计算数组条件中的第2个条件〝高？〝，当判断区域中的值为高一、高二、高三时，条件均成立。那么将其对应单元格中的95、99、100求和，得到294。除此之外的条件区域中的其他单元格条件均不成立。

步骤3 ：公式中的条件数组{〝初？〝；〝高？〝}得到的值就是{293；294}，将其写入对应单元格即可。

4.1.3 多条件求和函数：SUMIFS

前面学习的SUMIF函数是单条件求和，本节学习SUMIFS 函数，是用于计算满足多个条件的总和的函数，SUMIFS函数的计算原理和SUMIF函数一样，只不过是条件更多了而已。

函数语法说明：

SUMIFS(sum_range,criteria_range1,criteria1,...[criteria_range127,criteria127])

● sum_range：必需参数。要求和的单元格区域，不能是数组。

● criteria_range1：必需参数。作为criteria1 测试的单元格区域，不能为数组。

● criteria1：必需参数。定义使criteria_range1中的那些单元格符合设定的条件，支持比较运算、数组、通配符等匹配方式。

注意 criteria_range1 和 criteria1 是用于搜索某个区域是否符合特定条件的搜索对。一旦在该区域中找到了项，将计算sum_range中的相应区域的和。

● criteria_range2，criteria2，…：可选参数。更多的条件区域及其关联条件，最多可以输入 127 个区域/条件对。

实例41 求二级苹果的总数量（SUMIFS函数的普通应用）

问题描述： 图4-14所示的表中有“等级”“品名”“数量”3列数据，现在要求在E3单元格中统计出二级苹果的总数量。

解决方法： 本实例有两个条件：一是等级必须为“二级”；二是品名为“苹果”，本实例可以使用多条件求和函数SUMIFS来完成。

操作步骤： 如图 4-15 所示，首先将鼠标光标定位在 E3 单元格，输入公式=SUMIFS（C2：C7，A2：A7，〝二级〝，B2：B7，〝苹果〝），再按Enter键完成计算。

步骤1： 将C2:C7写入SUMIFS函数的第1参数中，作为条件成立的求和区域。

步骤2： 第1个条件，等级为“二级”，在SUMIFS函数的第2参数中写入A2：A7作为第1个条件的判断区域，第3参数写入条件为〝二级〝，这样完成第1对条件的编写。

步骤3： 第2个条件，品名为“苹果”，在SUMIFS函数的第4参数中写入B2：B7作为第2个条件的判断区域，第5参数写入条件为〝苹果〝，这样完成第2对条件的编写。

通过分析图4-15表格中的数据发现，对应等级和品名两个条件同时成立的只有第2条和第6条，其他的每条记录，要么条件都不满足，要么只满足其中一个条件，所以最后将第2、6条对应的45、24求和，结果为69。

另外，也可以用数组思路来完成此实例，公式为=SUM（（A2：A7=〝二级〝）*（B2：B7=〝苹果〝）*C2：C7）。

实例42 求各等级、各品名的水果的总数量

问题描述： 图4-16左边的表中列出了“等级”“品名”“数量”3列数据，要求在图4-16右边的表中按不同等级和不同品名求总数量。

解决方法： 此实例的本质还是多条件求和，只不过等级条件的值分布在行方向，品名条件的值分布在列方向，所以可以使用SUMIFS函数来完成。

操作步骤： 使用SUMIFS函数的普通方法、数组法、区域法3种方式来完成。

普通方法：如图4-17所示，首先将鼠标光标定位在F2单元格，输入公式=SUMIFS($C:$C,$A:$A,F$1,$B:$B,$E2)，然后将公式向右、向下填充。此公式中的条件不是直接写在公式里面的，而是采用引用单元格的方式。这样可以把每一种级别、每一种品名的数量汇总。

数组法：如图4-18所示，首先选择F2：G4单元格区域，然后在编辑栏中输入数组公式=SUMIFS （C：C，A：A，{〝一级〝，〝二级〝}，B：B，{〝香梨〝；〝苹果〝；〝香蕉〝}），按Ctrl+Shift+Enter组合键。此条件以数组的形式显示在公式中，这样计算的结果也是数组形式。

区域法：可以将数组形式的条件换成对应的单元格区域。如图4-19所示，选择F2:G4单元格区域，在编辑栏中输入数组公式=SUMIFS(C:C,A:A,F1:G1,B:B,E2:E4)，按Ctrl+Shift+Enter组合键。此方法比数组法要好，也达到了同样的效果，当引用的条件单元格的值发生变化时，最后求和的值也会自动重新计算。

实例43 统计广东科技公司的总销售额（万元）（通配符应用）

问题描述： 图4-20的“2017年客户销售额（万元）”表中列出了各单位的企业性质，以及销售额相关信息，现在要求统计出单位包含“广东”关键字，同时企业性质是“科技”关键字的销售总额。

解决方法： 虽然此实例可以有多种不同的解决方法，但最简便的方法还是使用SUMIFS函数来完成。

操作步骤： 如图 4-21 所示，首先将鼠标光标定位在 E3 单元格，输入公式=SUMIFS（C3：C9，A3：A9，〝*广东*〝，B3：B9，〝科技〝），然后按Enter键确定。

步骤1： C3:C9为最终求和的单元格区域。

步骤2： 第1组条件是判断A3：A9单元格区域中是否包含“广东”，通配符写法是〝*广东*〝。

步骤3： 第2组条件是判断B3：B9单元格区域中是否包含“科技”，写法是〝科技〝。

步骤4： 两组条件同时成立的是第1、3、5条记录，对应返回C3:C9单元格区域的33、15、24三个值，求和结果为72。 QQijrCJeKy5hDNL7NCIEXgReVAsG7/ZGfDmzYjRLoAv118oKi15o3mUUDOGMtay4