这个函数看起来很简单，却一不小心就用错了！

点击蓝字【秋叶 Excel】

发送【交流】

立即进【秋叶同学会】交流Excel！

本文作者：明镜在心

本文编辑：竺兰

求和、查找、计数这三类函数，可谓是最为常用的 Excel 函数。

而在求和统计中，以SUM、SUMPRODUCT、SUMIF 单条件求和或者 SUMIFS 多条件求和使用的最多。

虽然，SUM、SUMPRODUCT 也可以使用数组的方式实现条件求和，但是对于初学者来说，内存数组学习起来有一定难度，而且在数据相对较多的时候，使用内存数组会使表格非常卡顿。

所以对于条件求和，建议优先使用 SUMIF 或者 SUMIFS。

SUMIF 的语法结构是：
SUMIF (条件区域，条件，求和区域)

SUMIFS 的语法结构是：
SUMIFS(求和区域，条件区域 1，条件 1, 条件区域 2，条件 2，……)

此处的条件对，最多可以写 127 对。

有的小伙伴们在使用他们的时候，经常会遇到一些无法求和或者求和出错的情况。

下面我们一起来看看这两个函数到底有哪些坑！

情形一：长数字

如下图，是一个网购订单明细表。

要求是：根据日期和订单号统计出总金额。

这个问题，看似还是比较简单的吧。 通常我们可以写出如下公式：

公式如下：

公式解析：

在【A】列中查找【E2】单元格的日期，并且在【B】列中查找【F2】订单号，如果同时符合这两个条件的话，对【C】列的金额进行求和。

仔细观察下，【G2】返回的结果是错的！ 它将【C2:C4】单元格区域的数量一并加起来了。

这是为什么呢？

究其原因是：这类以纯数字作为订单号，而且数字的长度超过了 15 位的数据， 该函数会将超过 15 位数字之后的数字全部视为 0。

以上案例中，订单号一共由 19 位数字组成。 也就是说： 在统计的时候，只要订单号前 15 位数字完全相同，都会将其数量统计在内。 即将【C2:C4】的金额都统计， 如下图：

解决方法：可以在订单号之后连接一个通配符 （星号*） 即可。

这样连接一个星号 （*） ，统计结果就正确无误了。

其原理是：纯数字连接一个通配符之后，将原来的数字强制变成文本，最终以文本的方式来进行统计。而文本没有数字位数的限制。

PS：如果订单号位数不一样，连接通配符（星号*）时要特别注意下，有时会产生统计错误。比如下面这样：

公式同上。

要统计的数字编号有 15 位以内和 15 位以上的订单号，连接通配符之后，统计结果出错了。

原因是【F2】虽然与【B3】内容完全一样，但是连接通配符之后，会认为【F2】和【B2】【B4】的订单号也一样了，大家在实际应用的时候需要注意下。

情形二：通配符

下面是一个产品规格明细表：

需要求出产品的总数量。

公式的基本思路还是跟上面一样。

公式如下：

公式解析：

在【A】列中查找【E2】单元格的品名，并且在【B】列中查找【F2】中的规格，如果同时符合这两个条件的话，对【C】列的数据进行求和。

统计的结果是将第 2 行、第 5 行和第 7 行的数量全部相加了！

这又是为什么？

其实原因就在这个通配符本身。

这里查找内容是：10*30

中间的星号可以代表任意多个字符。 比如可以是： 100*30 或者 101*30 或者 10*130 等等。 只要开始是 10，结尾是 30 的规格，都将统计在内。

在这种情况下，我们想要得到正确的结果，需要去除通配符的特性。 把公式改成如下形式：

使用 SUBSTITUTE 替换函数将规格中的通配符星号前面加一个波形符 （~） ，就可以了。

该波形符的作用是将通配符变成普通字符来处理。

substitute 语法结构：

substitute(包含需要替换的文本,需要替换的旧文本,需要替换的新文本)

情形三：空白单元格

如下图，还是一个产品规格明细表。

由于其中的电机没有写规格，所以就以空单元格显示。

公式如下：

公式解析：

在【A】列中查找【E2】单元格的品名，并且在【B】列中查找【F2】的规格，如果同时符合这两个条件的话，对【C】列的数量进行求和。

结果电机的数量为 0！

又出错了？

这这这又是什么坑？

这个例子中又没有长数字，也没有通配符，这回到底是咋回事呢？

找了半天，问题终于找到了， 问题就出在空白单元格这里。

该函数对于空白单元格有特别的处理方法。 需要在 F2 单元格之后连接一对英文半角的双引号。

公式更改如下：

在 F2 后面连接一对英文半角的双引号，以代表空白单元格这个条件即可。

此时统计结果正确无误。

今天我们分享了 SUMIFS 这个函数的三个坑爹的情形。

❶ 处理长数字的编号，如订单号，银行卡号，身份证号。

❷ 处理带有通配符的数据。

❸ 处理带有空白单元格的数据。

对于如下列举的函数也有同样的问题， 小伙伴们在使用时，要特别留意下哦！

如果你想系统性学习 Excel，掌握更多 Excel 技能。

正好，我们《秋叶 Excel 3 天集训营》专为职场人准备，全部基于职场真实表格案例设计，还有很多超实用 Excel 技巧教学。

每天学习大概30 分钟，从日常的功能出发，全程演示，一课一练，夯实进阶每一步。

秋叶 Excel 3 天集训营

每天学习 30 分钟

你也有可能成为 Excel 高手！

现在就扫码报名吧！

好了，今天的分享就是这些，如果 喜欢我的文章，请点赞&转发支持一下吧！ 我们下次再见！

遇到有价值的文章

点点在看支持一下 !

动动小手

分享给朋友~