用SUMIF对超15位的代码进行条件求和，出错了，原因是....

用SUMIF对超15位的代码进行条件求和，出错了，原因是.... 

2017-10-29 23:01

一、问题

有读者朋友问：

用SUMIF进行条件求和时，如果统计的条件是超15位的代码，就会出错，比如下图要统计D2单元格身份证号在A列出现的个数、对应B列的金额之和，

E9单元格公式为：

=COUNTIF(A2:A14,D2)

E10单元格公式为：

=SUMIF(A2:A14,D2,B2:B14)

公式并没有错，但计算结果却是错的！这是什么原因？应该用什么公式？

二、原因

要弄清这个原因，首先要从Excel的最大数字位数讲起，在Excel中，数字的最大位数15位，超过15位的数字，会自动将后面的位数变为0，这一点，我们在输入身份证号时，应该遇到过，每次输入18位身份证号，后面三位会自动变为0。解决方法时将单元格设置为文本格式再输入，或者在身份证号前加英文的'符号。

本文中的问题也是这个原因。可能大家会说，A列不是已经将其设为文本格式了吗？为什么还是会将其作为数字对等呢？这应该是与Excel的特点有关，Excel并不象Power Query对数字类型有严格的要求一样，它会根据情况自动转换数据类型，比如下图，

A1、A2是文本，我们在A3设置公式

=A1+A2

那么公式就会出错，因为文本是无法相加的。

但是，

B1和B2同样是文本（文本型的数字），B3单元格的公式还是

=B2+B3，此公式却不会出错，Excel会自动将B1、B2自动转换为数字类型，然后相加。

因而，当遇到文本格式的身份证或编码时，Excel会自做聪明的将其转换为数字，因而数字15位以后的自动变为0，因而用COUNTIF或SUMIF统计个数或求和，其结果就会出错。

三、怎么办

解决方法：

1、在条件中后加通配符

添加通配符后，Excel就会将其视为文本

=COUNTIF(A2:A14,D2&"*")

=SUMIF(A2:A14,D2&"*",B2:B14)

2、使用SUMPRODUCT函数

=SUMPRODUCT((A2:A14=D2)*1)

=SUMPRODUCT((A2:A14=D2)*B2:B14)

提醒：

如果将统计个数的公式写为=SUMPRODUCT(A2:A14=D2)，其计算结果会是零，因为公式实际上是=SUMPRODUCT({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE})

如果TRUE和FALSE如果不对其进行运算时，SUMPRODUCT不会将其视同1和0对待的，所以，需要写成=SUMPRODUCT((A2:A14=D2)*1)