2.3.3　条件求和SUMIF与SUMIFS

SUM函数会对选定区域中的所有数值进行求和，甚至在进行筛选后，也会把隐藏的数据计算在内。如果想对满足某一条件的数据求和，可以使用SUMIF函数。

SUMIF函数是由SUM和IF函数组合而成的，意思就是对范围中符合指定条件的值求和，如图2-44所示为SUMIF函数的含义图解。

函数语法为：SUMIF(range,criteria,sum_range)

· range参数：为条件区域。

· criteria参数：是求和条件（criteria的中文意思是：标准、条件）。

· sum_range参数：求和区域。

SUMIF函数的第二个参数criteria非常重要，也是一个难点，下面重点讲解。它是用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。例如，条件可以表示为32、">32"、B5、"32"、"苹果" 或TODAY()。

1. SUMIF函数条件的构造思路

SUMIF函数的核心在于第二个参数criteria的构造，掌握了条件的构造，基本上就称得上灵活掌握了SUMIF函数。我们再把SUMIF函数第二参数条件的形式进行汇总归纳，如图2-45所示。

接下来通过两个经典的案例来掌握SUMIF函数条件的构造思路。

案例1： 对特定人员的奖金求和。

如图2-46所示，需要求出特定人员的奖金合计，在G2单元格中输入公式=SUMIF(B$2:B$13,F2,D$2:D$13)，然后将其复制填充到G3单元格，公式就会变成=SUMIF(B$2:B$13,F3,D$2:D$13)。

参数1是条件区域，也就是要对B$2:B$13区域的值进行条件判断，这里使用混合引用，是确保函数向下复制时，条件区域不会变；参数2是求和条件，这里直接引用单元格中的值，意思是使参数1满足此条件（等于F2单元格中的值，公式中作为条件的“=”可以省略）；参数3是求和区域，指的是在满足条件的情况下，对哪个区域进行求和。

综合来看，公式的意思就是在B$2:B$13区域中，求等于F2单元格所对应的奖金之和。而F2向下复制时，会变成F3，所以就能依次求出雷哥、李杰对应的奖金之和。

案例2： 求图2-47的成绩单中大于85分的成绩之和。

从这个案例的描述中可以解读出以下信息：一是条件区域是成绩单中的成绩，二是判断条件为大于85，三是求和区域为成绩单中的成绩。也就是说，求和区域和条件区域是完全相同的。如图2-47所示，本例只使用了SUMIF函数的两个参数，公式为=SUMIF(B2:B7,">85")，事实上，这里的B2:B7既是“条件区域”，又是“求和区域”。

思考： 同样是SUMIF函数，为什么案例1中条件区域（参数1）用的是行绝对引用，而案例2却可以用相对引用？

其实，函数中的参数是用相对引用还是绝对引用（或者混合引用），跟函数本身没有关系，而是取决于“公式是否需要复制填充、批量求解多个数值”。如果需要，通常来说，公式中引用的数据区域使用绝对引用，以确保这个区域不发生变化；如果公式不需要批量求解，使用相对引用或者绝对引用均可。

2. SUMIF函数高级应用

案例3： SUMIF升级玩法——多行多列精准求和。

我们在使用SUM函数时知道，SUM可以忽略区域中的文本，这样在多区域求和时就非常方便。SUMIF函数是不是具有同样的特性呢？如图2-48所示，我们要求出Lily三个月的业绩之和，但是三个月的业绩在不同的列中。

其实，SUMIF函数具备SUM函数的包容性，允许设置多行多列的条件与求和区域，实现多行多列精准求和。不要把多列数据当作障碍，大胆地选取条件区域和求和区域作为SUMIF的参数，在I2单元格中输入公式：=SUMIF(A1:E8,H2,B1:F8)，如图2-48所示。

这里条件区域选择A1:E8，求和区域选择B1:F8，这有什么特别的用意吗？能够选择整个区域吗？我们来看一下SUMIF函数的实现原理。

这里有一个核心知识点：SUMIF函数第三参数的定位原理。SUMIF函数的第三参数单元格区域起作用的就是左上角那个单元格，此单元格的作用是定位定点，只要有此定位点，SUMIF就会自动以此单元格为原点，按照第一参数区域符合条件的单元格的坐标，找到同样坐标位置的单元格，并对其数值求和。

这个定位原理应该如何解读？我们结合图示来分析。如图2-49所示的蓝色框区域和红色框区域分别为条件区域（A1:E8）和求和区域（B1:F8），作为求和区域，相比条件区域在行方向上整体向右移动了一列，这样Excel就能顺利找到销售员对应的业绩（业绩也是在销售员的右侧），所以两个区域都不能选择完整的数据区域。

我们跳出本例，再从一般的情况来归纳SUMIF函数第三参数的定位原理，结论如下。

①SUMIF函数的第三个参数以左上角单元格为定位点，SUMIF会以此单元格为原点，自动扩展出一个大小和形状与第一参数一致的单元格区域。

②SUMIF按照第一参数区域符合条件的单元格的坐标，在第三参数区域找到同样坐标位置的单元格，并对其数值求和。

③SUMIF函数的第一参数与第三参数数据区域的大小和形状必须一致，但数据区域的起点可以不同。

④SUMIF函数的第三参数可以只选取左上角的单元格，无须选取整个求和区域。

如图2-50所示，黑色框是第一参数range的数据区域，红色框是第三参数sum_range的数据区域，一旦确定了第一参数的区域，第三参数只需要确定定位点C4即可，确定之后，SUMIF函数在计算时会自动对第三参数进行扩展，扩展之后就是红色的区域。

案例4： SUMIF函数错行、错列求和。

如图2-51所示，每个销售员对应两个产品，我们想要求出李想对应的B产品的销量合计。可以看出，其实就是求红色框内三个产品对应的销量之和，用SUMIF函数应该怎样写公式呢？

根据SUMIF函数第三参数的定位原理，回到这个问题本身，销售员和产品存在错行的情况，我们可以设置条件区域和求和区域也进行相应的错行，就能实现表中的错行效果。如图2-52所示，在F3单元格输入公式=SUMIF(B2:B16,"李想",D3:D17)。也就是说，第一参数条件区域从B2单元格开始，第三参数求和区域从D3单元格开始，两者形成了一个错行的效果。

从另一个层面来看这个问题，其实这是一个多条件求和的问题，条件1是销售员李想，条件2是产品B。既然是多条件求和，当然也可以用多条件求和函数SUMIFS来计算，但如果用SUMIFS函数，源数据表需要改造，销售员列需要完整地把所有的姓名都填满。换句话说，销售员和产品要有一一对应的关系。

掌握了错行求和的原理，错列求和是相同的道理，我们在对应的视频中有关于错列求和的案例讲解，大家可以进一步练习。

案例5： SUMIF函数求最终成绩之和。

如图2-53所示的表记录了不同人员不同次数的考试成绩，现在需要求出每个学员最后一次考试成绩之和。每个人的考试次数不一样，意味着最后一次成绩出现的位置没有统一性。但是，最后一次成绩又都有一个特点，那就是它们都出现在同一行空白单元格左侧第一个位置。利用这个特点，我们可以这样写公式，在A9单元格输入公式=SUMIF(B2:G6,"",A2:F6)，如图2-53所示，第二参数只有一个双引号，表明参数为空，代表对应的条件为空，也就是空单元格的意思。

整个公式的意思就是：如果B2:G6单元格区域中满足等于空值的条件，就计算A2:F6数据区域与之对应的单元格中的值之和。由于A2:F6定位点相比B2:G6区域向前移了一列，所以求和的单元格相比B2:G6区域中空值单元格也要向前移一列，这样就得到了A2:F6区域每一列中最后一个值的和。