如果某个范围的单元格不满足此要求，则SUMPRODUCT((range = {"this";" that}} *(其他条件))返回N/A. [英] SUMPRODUCT((range={"this";"that"})*(other conditions)) return N/A if some cell of range does not meet this or that

问题描述

数据集:

A|B

1|a     
2|b     
3|c

公式:

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

sumproduct返回值NA不存在.我不明白为什么.我希望它返回2.

The sumproduct return NA there. I don't get why. I want it to return 2.

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

If I add c to the condition it correctly returns 3.

我想念什么?

推荐答案

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

You are comparing an array of size 1x3 to an array of size 1x2 which is not allowed. (If you compare two vertical arrays, they must be the same size. This is why the problem is fixed when you add "c" to the formula.)

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

You need to compare a 1x3 array to a 2x1 array.

此表达式:

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

返回:

{TRUE;TRUE;#N/A}

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

SUMPRODUCT cannot handle logical (TRUE/FALSE) values so you must add 0, multiply by 1, or perform double negative operator to change to an array of numerical values.

所以...这个:

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

返回:

{1;1;#N/A}

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

Performing SUMPRODUCT on this will still return #N/A since the array contains #N/A.

但是，如果您这样做:(请注意用逗号代替分号)

But if you do this: (Note the comma instead of semicolon)

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

现在，您得到了:

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

请注意，这将返回2x3数组.

Note this returns a 2x3 array.

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

Perform double negative operator and you get this:

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

现在执行SUMPRODUCT将返回2，如您所愿.

Now performing SUMPRODUCT will return 2, as you expect.

您需要的最终公式是:

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

这篇关于如果某个范围的单元格不满足此要求，则SUMPRODUCT((range = {"this";" that}} *(其他条件))返回N/A.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！