excel - 如果范围的某些单元格不满足这个或那个条件，SUMPRODUCT((range= {"this";"that"})*(其他条件)) 返回 N/A

Question

数据集:

A|B

1|a     
2|b     
3|c

公式:

=SUMPRODUCT((B1:B3={"a";"b"}))

sumproduct 在那里返回 NA。我不明白为什么。我希望它返回 2。

如果我将 c 添加到条件中，它会正确返回 3。

我缺少什么？

Answer 1

您正在将大小为 1x3 的数组与大小为 1x2 的数组进行比较，这是不允许的。 (如果比较两个垂直数组，它们的大小必须相同。这就是为什么在公式中添加 "c" 时问题就得到解决的原因。)

您需要将 1x3 数组与 2x1 数组进行比较。

这个表达式:

B1:B3={"a";"b"}

返回:

{TRUE;TRUE;#N/A}

SUMPRODUCT 无法处理逻辑 (TRUE/FALSE) 值，因此您必须加 0、乘以 1 或执行双负运算符更改为数值数组。

所以...这个:

--(B1:B3={"a";"b"})

返回:

{1;1;#N/A}

对此执行 SUMPRODUCT 仍将返回 #N/A，因为数组包含 #N/A。

但是如果你这样做:(注意逗号而不是分号)

B1:B3={"a","b"}

现在你得到:

{TRUE,FALSE;FALSE,TRUE;FALSE,FALSE}

请注意，这将返回一个 2x3 数组。

执行双负运算符，你会得到:

{1,0;0,1;0,0}

现在执行 SUMPRODUCT 将返回 2，如您所料。

您需要的最终公式是:

= SUMPRODUCT(--(B1:B3 = {"a","b"}))