☰

向 SUM 公式添加条件会导致 #N/A 错误

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
向 SUM 公式添加条件会导致 #N/A 错误

我正在尝试根据一些条件对范围进行总结。

当我进入

=SUM(IF((INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N))/INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N)),0))

公式返回有效结果。

但是，当我添加条件时

LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When"

如下：

=SUM(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N))/INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N)),0))

然后我收到#N/A 错误。

即使我删除了除法运算，例如

=SUM(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N)),0))

我仍然收到#N/A 错误。

当我在 Z 列中进行计数时，如下所示：

=COUNT(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))))

我确实得到了有效的结果，所以我知道至少有一些单元格满足我上面提到的条件。

那么，为什么我在求和时会出现#N/A 错误？

我特别感兴趣的是了解为什么一旦添加至少满足一次以上且在 COUNT 公式中有效的条件，公式就会不再有效。

最终目标是确定产品每月收取的平均佣金。我删除了按月分摊佣金的条件，以避免使上述已经令人困惑的公式过于复杂。使用这些数组公式的理由是它们是动态的，每月只计算一次，并且需要每月由无法添加辅助列或解释公式工作原理的用户转移到新的工作簿中。使用辅助列使我实现了目标，但不幸的是，在这种情况下辅助列不是选项

有任何想法吗？

答案1

在第一个公式中你使用了 COUNT，即

COUNT(CommissionDetail!$N:$N)

但在附加部分你使用了 COUNTA，即

COUNTA(CommissionDetail!$N:$N)

如果 N 列（例如标题行）中有任何文本值，那么这些文本值将不会给出相同的数字（因此出现 #N/A 错误），因为 COUNT 仅计算数字，而 COUNTA 计算所有条目。

我建议您始终更改为 COUNTA，因为如果您有标题行，使用 COUNT 将忽略最后一行

您可能还可以简化一点，不认为您需要SEARCH新条件下的部分，为什么不直接寻找以当且仅当 (.....并且您可以使用IFERROR而不是检查空白或零作为除数，即包括新条件（和整个建议的 COUNTA）：

=SUM(IFERROR(IF(LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),13)="As and When (",INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNTA(CommissionDetail!$N:$N))/ INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNTA(CommissionDetail!$N:$N))),0))

已确认CTRL++SHIFTENTER

....进一步改进以使公式更具可读性，就是定义重复元素，即

=COUNTA(CommissionDetail!$N:$N)

作为命名范围，例如行数然后公式又变短了，即

=SUM(IFERROR(IF(LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&RowCount),13)="As and When (",INDIRECT("CommissionDetail!$AF$2:$AF$"&RowCount)/ INDIRECT("CommissionDetail!$Z$2:$Z$"&RowCount)),0))

相关内容