Excel函数学习42：SUMIFS函数

SUMIFS函数的语法说明、注意事项及应用实例。...





SUMIFS函数对一组给定条件指定的单元格求和。



图1

什么情况下使用SUMIFS函数?

SUMIFS函数用于计算某区域中满足一个或多个条件的单元格的总和。它能够：

• 多条件求和
• 配合VLOOKUP函数查找多个列中的值并获取相对应的值
• 按每行汇总

SUMIFS函数语法

SUMIFS函数有多个参数，其语法如下：

• SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)
[list]
• sum_range: 必需，指定求和的单元格区域。
• criteria_range1: 必需，参数criteria1测试的单元格区域。
• criteria1：必需，指定对参数criteria_range1中的哪些单元格求和的条件。
• 在criteria_range1中搜索满足criteria1的项，一旦找到，就会计算sum_range中相应值的和。
• criteria_range2,criteria2,…. 可选参数，作用同criteria_range1和criteria1。最多可以使用127个区域/条件对。
• 求和时会忽略掉参数sum_range中的空或文本值。
• criteria可以是数字、表达式、单元格引用、文本或公式，可以使用通配符，不能多于255个字符。如果有文本、数学符号、逻辑符号（例如=、+、-、/、*），则应添加双引号。

SUMIFS函数陷阱

SUMIFS函数是Excel 2007中新增的函数，因此适用于Excel 2007及以上版本。

如果要测试文本字符串值，那么应对条件参数criteria1等的值使用引号，否则不会显示预期的结果，而是显示0。sum_range中的值包含TRUE和FALSE时，由于逻辑值的求值方式不同，可能会在将其相加时导致意外结果。

参数criteria_range与参数sum_range指定的单元格区域的行列数必须相同。

注意，SUMIFS函数和SUMIF函数的参数顺序。参数sum_range在SUMIFS函数中是第1个参数，而在SUMIF函数中是第3个参数。

示例1: 多条件求和

如下图2所示的产品销售数据表。



图2

计算东区吴小花销售的产品数量，公式为：

=SUMIFS(D2:D12,B2:B12,"=东区",C2:C12,"吴小花")

公式在单元格区域B2:B12中查找“东区”，在C2:C12中查找“吴小花”，然后计算D2:D12中同时满足这两个条件的单元格中数值的总和，结果为154。

计算东区除香蕉以外的产品销售数量，公式为：

=SUMIFS(D2:D12,A2:A12,"香蕉",B2:B12,"东区")

公式在Criteria1中使用排除香蕉，在B2:B12中查找东区，然后计算D2:D12中同时满足这两个条件的单元格中数值之和，结果为135。

计算由吴小花和孙大壮销售的以香开头的产品的数量，公式为：

=SUMIFS(D2:D12,A2:A12,"=香*",C2:C12,"吴小花")+SUMIFS(D2:D12,A2:A12,"=香*",C2:C12,"孙大壮")

公式中使用了通配符*，作为条件查找香开头的产品名。因为条件既包括了AND条件，又包括了OR条件，所以使用了两个SUMIFS函数来实现目的。

示例表明，可以在SUMIFS函数的查找条件中使用通配符（?或*），来查找近似项。其中，问号匹配任何单个字符，星号匹配任意数量的字符。如果要查找问号或星号本身，在符号前面加上~号。

计算香梨、香蕉和苹果的销售数量之和，公式为：

=SUM(SUMIFS($D$2:$D$12,$A$2:$A$12,{"香梨","香蕉","苹果"}))

在公式中，我们使用了花括号将要求销售数量的产品列出来，并使用SUM函数来求和。如果不加上SUM函数，我们将只会得到香梨在表中第1次出现的数量。

示例2：配合VLOOKUP函数查找多个列中的值并获取相对应的值

示例数据如图3所示。



图3

现在，通过城市和行政区获取所在的省份，如图4所示。



图4

在单元格D12中输入公式：

=VLOOKUP(SUMIFS($A$2:$A$8,$B$2:$B$8,B12,$C$2:$C$8,C12),$A$2:$E$8,5,0)

下拉至D14，结果如图5所示。



图5

注意，使用SUMIFS 函数必须保证列A中的编号没有重复值且是数值，这样才能实现正确的查找。当然，这个技巧也可以用于到日期。

示例3：按每行汇总

示例数据如图6所示，要求每天根据区域和物品进行求和汇总。例如，2018年3月8日西区钢笔的数量为78，但从2018年3月1日起至3月8日西区钢笔的总数量为247。如果列C为空，则汇总到该日期为止所在区域的总数量，如2018年3月4日东区物品总数量为339。



图6

在单元格E2中的公式为：

=SUMIFS(D$2:D2,B$2:B2,B2,IF(C2="",B$2:B2,C$2:C2),IF(C2="",B2,C2))



图7

或者将SUPRODUCT函数与SUMIFS函数配合使用：

=SUMPRODUCT(SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,IF(LEN(C2)>0,C2,{"*",""})))

或者将SUMIF函数与SUMIFS函数配合使用：

=IF(C2="",SUMIF(B$2:B2,B2,D$2:D2),SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,C2))

    关注 完美Excel