问题概述
如果使用 SUMIF 或SUMIFS函数的条件值文本长度超过了15位,Excel的SUMIF或SUMIFS函数可能无法正确匹配这样长的文本。
如下图所示,用SUMIF函数,条件求和A列中相同发票号码对应的合计金额数,结果显示不正确。
原因分析
主要原因是在Excel中,数字的最大位数15位,超过15位的数字,会自动将后面的位数变为0,所以一般超过15位的数字在Excel中通常以文本型数字存在。
输入长位数字文本时,一般是将单元格设置为文本格式再输入,或者在身份证号前加英文的'符号,不然,会自动将16位开始的数字变为0。
用COUNTIF或SUMIF统计个数或求和,系统会自动将全部是数字的文本字符串自动转化为数字,但是他又只能识别15位数字,后面的数字会自动变成0。
如下图所示,23332000000009249998,23332000000009258793,23332000000009277638这几个单元格,前15位数字都是233320000000092,所以用SUMIF 函数求和计算时候,导致合计金额都是113810.96
同理,23332000000009340006前15位数字是233320000000093,只是恰好查找出了正确数值
解决方法
最简单的解决方法是,在公示中的条件后加通配符——&"*"
比如说公式本来是:=SUMIF(A:A,F2,B:B),修改为:=SUMIF(A:A,F2&"*",B:B)
=SUMIF(A:A,F2&"*",B:B)
, 则用sumif函数查找时,系统就不会自动将全部是数字的文本字符串自动转化为数字,而是认定为文本型数字,这样就可以精确查找合计金额了。
同理,多条件求和函数SUMIFS,或者计数函数COUNTIF、COUNTIFS也可以在条件值后面加上通配符&"*"解决不能精准计算和计数的问题。