史上最全条件求和函数SUMIF教程

在职场办公中,往往需要有条件地对数据进行汇总。SUMIF函数是工作中经常使用的条件和函数之一。

本文介绍了SUMIF函数丰富的用法,方便您在自己的实际工作中直接学习和使用。

1 1 sumif函数的基本语法分析

SUMIF函数可以对满足指定条件的范围内的值求和。该函数具有非常强大的条件求和功能,在工作中应用广泛。它的基本语法是:

SUMIF(范围,标准,

=SUMIF(B2:B9,& # 8221;女& # 8221;、C2:C3)

=SUMIF(B2:B9,& # 8221;女& # 8221;,C2:C99)

=SUMIF(B2:B9,& # 8221;女& # 8221;,C2)

三个公式返回的结果是一致的。SUMIF函数的sum_range参数中的单元格数目与range中的单元格数目不同,但sum_range的范围是根据C2:C9计算的,即C2是起始单元格,并扩展到与B2:B9大小和形状相同的单元格。等效于以下公式:

=SUMIF(B2:B9,& # 8221;女& # 8221;,C2:C9)

波动性将导致工作表的重新计算,这将花费比预期更长的时间。这种情况在工作中要尽量避免。

(5)5)SUMIF函数中criteria参数的格式会限制其选择条件之和的范围。也就是说,如果第二个参数是数值,SUMIF函数只计算第一个参数是数值格式的单元格对应的求和面积,而忽略文本、逻辑值、错误值等其他格式。利用SUMIF函数的这个特性,我们可以剔除错误的值,并对它们求和。

2SUMIF函数统计单字段单条件求和

当然,工作中最常见的要求是单个条件的总和。SUMIF函数在这方面是得心应手的!

先说SUMIF函数统计单个字段和单个条件的和,也说明一下SUMIF第三个参数默认时的操作方式和原理。

SUMIF函数的单条件求和应用广泛。很多情况下,当条件区域和求和区域重合时,公式书写可以简化。下面用一个案例来介绍具体的方法。

史上最全条件求和函数SUMIF教程-大盘站插图

要求从数据源中统计出90分以上分数的总和,先给出公式。

=SUMIF(B2:B12,& # 8221;& gt90″)

3SUMIF函数统计单字段多条件求和

在上一个教程中,我们学习了SUMIF函数的单条件求和。那么,当工作中出现对某个领域的并行多条件求和的需求时,该如何处理呢?

下面结合下面的案例详细介绍一下。

史上最全条件求和函数SUMIF教程-大盘站插图1

表格的左侧是数据源区域。要求统计北京分公司、上海分公司、广州分公司的总销售额。如果你只想要一个分公司(比如北京)的销量,那就很简单了。公式是

=SUMIF(A2:A12,& # 8221;北京& # 8221;,B2:B12)

多户呢?当然,最直接的方法是这样的:

=SUMIF(A2:A12,& # 8221;北京& # 8221;,B2:B12)+ SUMIF(A2:A12,& # 8221;上海& # 8221;,B2:B12)+ SUMIF(A2:A12,& # 8221;广州& # 8221;,B2:B12)

这是唯一的方法吗?如果要数的分支数增加,公式不是越来越长吗?

当然会有更好的办法!

给出这里使用的公式:

=SUM(SUMIF(A2:A12,{ & # 8220;北京& # 8221;,”上海& # 8221;,”广州& # 8221;},B2:B12))

4SUMIF函数计算前三个分数,并

在之前的课程中,我们学习了SUMIF函数的单条件和多条件求和。那么当我们在工作中遇到涉及数值的问题时,应该用什么思路去解决呢?

在下面的案例中,我们将使用SUMIF函数结合LARGE函数来解决一个极值统计问题。

史上最全条件求和函数SUMIF教程-大盘站插图2

表格左侧是数据源区域,要求统计前三个结果的总和。

我们可以分两步来思考这个问题。第一步,用公式从数据中提取前三个等级,第二步,求和。这样数据源发生变化,前三个结果会随着公式结果动态更新,从而始终保证结果正确。

这是公式。

=SUMIF(B2:B12,& # 8221;& gt”& amp大(B2:b 12.4)

5SUMIF函数模糊条件的和

有时候要根据模糊条件求和,SUMIF函数支持使用通配符。这里用一个案例来介绍模糊条件求和的方法。

史上最全条件求和函数SUMIF教程-大盘站插图3

表格的左侧是数据源区域。要求统计姓氏为“张”的员工,即姓名以“张”开头的员工的业绩总和。我会给出公式。

=SUMIF(A2:A12,& # 8221;张* & # 8221;,B2:B12)

6SUMIF函数根据日期间隔计数。

在我们的工作中,经常会遇到按日期区间计数的需求,比如从月初到当前日期的销售额,或者周年的销售额(比如持续5天)...如何达到这样的条件之和?

今天我们结合一个简单的案例来介绍一下基于日期区间的求和法。

史上最全条件求和函数SUMIF教程-大盘站插图4

公式如下所示。

=SUM(SUMIF(A2:A12,{ & # 8220;& gt=2016/4/1″,”& gt2016/4/5″},B2:B12)*{1,-1})

7SUMIF函数计算登记者的非空收据号。

作品中的数据来源可能来自各种渠道,有的是系统导出的,有的是手工填写收集的,有的是第三方机构提供的。数据源中的某些字段不可避免地会有空值或无效值。这时候往往需要排除这些无效记录进行统计。

今天我们结合一个简单的案例来说明如何使用SUMIF函数计算涉及非空值的求和条件。

史上最全条件求和函数SUMIF教程-大盘站插图5

登记人为空的记录都是无效记录,在统计入库数量时不会考虑入库数量。将只计算登记者的非空接收数量。

给出公式:

=SUMIF(A2:A8,& # 8221;*”,B2:B8)

8SUMIF函数列分隔分类摘要

SUMIF函数强大的条件求和功能,在很多工作场景中都有广泛的应用。无论是财务人员还是营销人员,他们都需要汇总数据源中跨列的条件,例如,在包含计划和实际销售额的表中汇总计划和实际完成的总和。

史上最全条件求和函数SUMIF教程-大盘站插图6

在这种情况下,每个业务员的计划数据和实际完成数据交替出现。最后要在黄色区域输入公式,完成对应的计划和实际总和的统计。

在H3单元格中输入以下公式,并将其填入H3单元格:I9。

=苏米夫(美元2元:加元2元,加元2元,B3元:G3元)

9SUMIF函数实现了查找引用的功能。

看了这个标题,有些家伙很不解。用VLOOKUP函数和INDEX+MATCH查引用不是他们的事吗?SUMIF为什么要来凑热闹?

你说得对。除了条件求和,SUMIF在某些场景下还可以实现查找引用的功能。

史上最全条件求和函数SUMIF教程-大盘站插图7

在本例的表格中,左侧是数据源区域,右侧黄色区域是公式区域。

为了根据销售员找到相应的结果,使用了一个公式。

给出公式(H2输入)

= SUMIF($ A $ 2:A $ 12,$G2,B$2:B$12)

10SUMIF函数消除误差值求和。

由于各种原因,我们在处理数据时不可避免地会遇到错误值。当数据源包含错误值时,通用求和公式也会返回错误值。那么如何才能在不影响条件求和的情况下消除误差值呢?

让我们来看看这个案例:

史上最全条件求和函数SUMIF教程-大盘站插图8

如果是职场小白,看这个数据源就蒙了,几乎各种错误值都会报出来。我该怎么办?

行家伸手就知道有没有。

给定公式,在单元格D2中输入以下公式

=SUMIF(B2:B12,& # 8221;& lt9e307 & # 8243)

11SUMIF函数计算非空入库日期的数量总和。

当我们遇到数据源时,不可避免地会有一些字段的值为空或无效。这时候往往需要排除这些无效记录进行统计。

之前,我们介绍了在文本数据中掺杂空值的处理方法。参见注册人非空收据的SUMIF函数统计。今天,我们将介绍在下一个日期数据中掺杂空值的处理方法。

下面用一个案例来说明如何使用SUMIF函数对求和条件中涉及的空值进行计数。

史上最全条件求和函数SUMIF教程-大盘站插图9

收货日期为空的记录都是无效记录,所以在统计收货数量时不会考虑,只统计非空的登记人的收货数量。

给出公式:

=SUMIF(A2:A8,& # 8221;& lt& gt”,B2:B8)

12SUMIF函数多栏区域条件求和

在前面的教程中,我们介绍了SUMIF函数的各种条件求和方法,都是条件区域只有一列,求和区域只有一列的场景。那么,如果条件区域和求和区域都是多列区域,该怎么办呢?

史上最全条件求和函数SUMIF教程-大盘站插图10

上图是某企业的员工人数信息表。雇员编号和相应的姓名放在多列中。需要根据B10:B12单元格中的员工姓名提取相应的员工编号。

给出一个公式

在单元格B10中输入以下公式,并将其复制到单元格B12。

=SUMIF(B$2:D$6,A10 A $ 2:C $ 6)