编按：

在Excel的日常操作中，我们最容易碰到的便是求和公式，但是在给数字求和时，总会遇到各种各样的问题，让人丈二和尚摸不着头脑。今天就让我们来系统性地给大家讲解一下，单纯求和中，最容易出现的一些小失误，希望大家可以举一反三。

今天和大家来聊聊Excel的几个关于求和的问题。可能有的同学就纳闷了，求和这么简单还用聊吗？不就是SUM函数。

如果真的这么简单肯定就没啥聊的了，不信就看看下面这些问题你会几个？

Ps：今天讨论的都是单纯的求和问题，并没有那种合并单元格求和、筛选求和、特殊的条件求和等等复杂的问题。

问题一：数字求和结果为0

假如有一列数字，使用SUM函数求和时却发现结果为0，通常有两种可能。

1.数字是文本格式

例如下图中的这种情况：

对于这种情况有三种方法。

方法1：数字的单元格左上角有个绿色小三角，可以通过小三角将数据转为数值类型，求和结果就正确了。

操作要点：一定要从第一个有绿色三角的单元格开始选择，是最简单的一种方法。

方法2：选中这一列，用分列功能进行处理。

操作要点：分列的时候直接点完成即可。

方法3：直接用公式=SUMPRODUCT(B2:B12*1)求和

公式中的（B2:B12*1）这部分是通过乘法运算将文本型的数字转换为数值，再利用SUMPRODUCT函数可以对数组求和的特性来解决问题的。

2.有不可见字符

这种问题常见于系统导出的数据，数据不是文本格式，但是求和结果也是零，例如下图演示的情况。

这种只是不可见字符中的一种类型，还有些不可见字符更加隐蔽，在编辑栏也看不出问题，遇到这种情况可以先使用LEN函数做个检测。

LEN函数的功能是得到单元格里的字符数，检查结果表示单元格里数据的长度，明显可以看出比实际数据多了一个字符，说明有一个不可见字符。

遇到这类情况，可以直接用公式=SUMPRODUCT(CLEAN(F2:F12)*1)求和，CLEAN(F2:F12)的作用是清除单元格中的不可见字符，再利用*1将清除后的数据变成一个数组，由SUMPRODUCT函数完成求和。

问题二：对带单位的数字求和

有些同学总是习惯在数据后面加上单位，当然也可能领导要求这样做的，造成的后果就是无法求和。

对于这类问题，正确的解决方法是先把单位去掉，然后用自定义格式加上需要的单位，这样就不会影响求和了，操作方法看动画演示。

当然也可以直接用公式=SUMPRODUCT(SUBSTITUTE(F2:F12,"元","")*1)进行求和。

这个公式首先是用SUBSTITUTE(F2:F12,"元","")将"元"字替换为空得到一组数字，然后再利用*1变成数组后由SUMPRODUCT完成求和。

上面的两个方法对于大多数情况来说都可以搞定的，如果你遇到的情况更加复杂，可以参考之前的教程：

https://mp.weixin.qq.com/s/tU_2rBy9XQA3OvXfu0u8RQ

问题三：数据中有错误值

数据中有错误值的情况简直是太常见了，有匹配不到结果返回错误值的，也有分母为零造成错误值的，还有用了一些复杂公式产生错误值的，这不是我们今天讨论的要点，我们只讨论当求和的数据中出现错误值时该怎么办？

下面这个图里模拟了三种错误值，直接求和的话结果也是错误值。

遇到这样的情况当然是要找出每种错误出现的原因，从源头去解决问题。

但是如果想直接在保留错误值的情况求和的话，也有三个公式可以使用。

公式1：=SUM(IFERROR(B2:B12,0))

利用IFERROR函数将区域中的错误值变成0，然后再用SUM函数求和，注意这个公式是数组公式，需要同时按Ctrl、shift和回车键完成输入。

公式2：=SUMIF(B2:B12,"<9e307")

公式中的9e307，表示9乘以10的307次幂，是一个非常大的数值，SUMIF只对小于9E307的数值部分进行求和，并且自动忽略区域中的错误值。本例中因为求和区域和条件区域相同，所以可以省略SUMIF函数的第三个参数。

公式3：=AGGREGATE(9,6,B2:B12)

AGGREGATE函数第一参数使用9，表示汇总方式为求和，第二参数使用6，表示忽略错误值。也就是在忽略错误值的前提下，对B2:B12区域进行求和。

问题四：循环引用导致求和结果为零

这是一种特殊的人为错误，从表面上看不出任何问题。

没有文本格式的数字，也没有不可见字符，但是求和结果是0。

实际上在编辑公式的时候，或者打开这个文件的时候，Excel会出现一个提示：

这就是在告诉你计算不正确的原因是因为存在了循环引用。

如果你不知道是哪个单元格出错的话，可以依次点击【公式】-【错误检查】-【循环引用】，就能看到有问题的单元格了。

然后再去检查公式，就能发现B13单元格里的公式是=SUM(B2:B13)，求和的范围包含了B13，只要将B13改成B12就OK了。

问题五：时间的求和问题

最后一类问题是涉及到时间的求和，例如在对一个人的加班时长求和时，结果可能不是你所希望看到的。

最后合计的加班时长竟然只有3:40，结果肯定有问题。

出现这种问题的原因是因为在Excel中时间累计到24小时以上就会自动向日期进位，也就是24小时变成1天，只有不足24小时的部分才会以时间的形式显示。

解决方法也有两个。

公式1：=TEXT(SUM(B2:B12),"d天h小时m分")可以将合计时长以x天x小时x分的形式显示。

公式2：=TEXT(SUM(B2:B12),"[h]小时m分")可以将合计时长以x小时x分的形式显示，[h]两边加上方括号就表示小时这部分不用向上进位。

今天分享的内容就是这么多，回到开头的那个话题，这些求和的问题你会几个呢？

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

将excel中的数据按照指定数量进行重复，非得用VBA才能实现吗？

如何在特定位置批量插入空行等12种实用办公技巧

4种删除excel重复值的小妙招，速收藏

Power Query的数据替换技巧比Excel函数更万能！

版权申明：

本文作者老菜鸟；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。