不一致的SUMPRODUCT返回值 [英] Inconsistent SUMPRODUCT return value
问题描述
在下面的例子中,这个公式是为了计算不是由塑料制成的苹果的数量。
= SUMPRODUCT( - (B4 = IF(NOT($ C $ 4:$ C $ 11))$ B $ 4:$ B $ 11)))
应该返回1.为什么返回2?
当我使用 F9 手动评估 SUMPRODUCT
中的数组时,我可以看到只有一个在那里;当我然后按输入,它返回1,这是我从一开始的预期。
最好的部分:如果我将完全相同的原始公式复制到其他单元格(未拖动 - 具有完全相同的单元格引用),则返回0或$ code> #VALUE! / code>错误。为什么结果会如此改变?
测试您的复印粘贴乐趣的数据:
水果由塑料制成
苹果FALSE
香蕉FALSE
李子FALSE
梨TRUE
苹果TRUE
香蕉FALSE
李子TRUE
pear TRUE
尝试使用 COUNTIFS
函数,因为您尝试根据多个条件进行计数:
= COUNTIFS B:B,B4,C:C,False)
What the heck is going on here? Is this a bug?
In the example below, this formula is meant to count the number of apples that aren't made of plastic.
=SUMPRODUCT(--(B4=IF(NOT($C$4:$C$11),$B$4:$B$11)))
It is supposed to return 1. Why is it returning 2?
When I manually evaluate the array inside SUMPRODUCT
using F9, I can see there's only one 1 there; when I then press Enter, it returns 1, which is what I expected from the start.
The best part: If I copy the exact same original formula to other cells (not dragged -- it has the exact same cell references), then it returns 0 or a #VALUE!
error. Why would the result change like that?
Test data for your copy-pasting pleasure:
Fruit Made of plastic
apple FALSE
banana FALSE
plum FALSE
pear TRUE
apple TRUE
banana FALSE
plum TRUE
pear TRUE
Try using the COUNTIFS
function instead, since you are trying to count based on multiple criteria:
=COUNTIFS(B:B, B4, C:C, False)
这篇关于不一致的SUMPRODUCT返回值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!