问与答129：如何对#N/A文本值进行条件求和？

excelperfect

Q：很有趣的一个问题！如下图1所示的工作表，在单元格区域A1:A2中，使用公式：

=”#N/A”

输入的数据。

在单元格A3:A4中，使用公式：

=NA()

输入的数据。

它们输出的结果看起来相似，但实质上是不同的：在A1和A2中是文本类型，而A3和A4中是错误类型。从数据的对齐方式上也可以反映出来。

图1

我现在如何使用SUMIF函数来求出文本“#N/A”值对应的列B中的数值之和？看起来简单，但实现起来却遇到了困难。我想要的答案是：3，但下列公式给我的答案是：12。

这些公式是：

=SUMIF(A1:A4,'#N/A',B1:B4)

SUMIF(A1:A4,'=#N/A',B1:B4)

=SUMIF(A1:A4,A1,B1:B4)

如何得到正确的答案3？

A：从上面的结果看得出来，在底层，SUMIF函数在进行比较之前会将这些标准参数中的每一个从文本类型强制转换为错误类型。

可以使用下面的SUMIF公式来实现：

=SUMIF(A1:A4,'*#N/A',B1:B4)

或者：

=SUMIF(A1:A4,'?N/A',B1:B4)

如者：

=SUMIF(A1:A4,'#?/A',B1:B4)

其中，通配符*和？让SUMIF函数来处理文本类型。

当然，这些公式并不严谨。例如，如果单元格A1包含公式=“abc#N/A”，那么由于*通配符，它将包含在总和中，而我们只希望包含纯“#N/A”值。为了尽可能考虑全面，剔除那些很少会出现的情况，可将公式调整为：

=SUMIFS(B1:B4,A1:A4,'?N/A',A1:A4,'#???')

也可以使用下面的数组公式：

=SUM((IFNA(A1:A4,'')='#N/A')*B1:B4)

你有其他解决方案吗？欢迎分享。

注：本文学习整理自colinlegg.wordpress.com，供有兴趣的朋友参考。