过滤器中的 Countif 1 和 -1 [英] Countif 1 and -1 in filter

问题描述

我正在尝试计算过滤数据中的 1 和 -1.-1 的计数是正确的，但对于 1 它也包括 -1 并显示不正确的计数.如果您查看附加的图像，1"的计数应该是 1，而不是显示 3，这意味着它也在计数 -1.更多信息请参考图片

I am trying to count 1 and -1 in filtered data. The count for -1 is correct but for 1 it includes -1 as well and shows the count which is incorrect. If you look at the image attached the count for "1" should be one instead it is showing 3 which means it is counting -1 as well. For more info refer the image

公式:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-MIN(ROW(B2:B11)),,1)),ISNUMBER(SEARCH("1",B2:B11))+0)

公式:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-MIN(ROW(B2:B11)),,1)),ISNUMBER(SEARCH("-1",B2:B11))+0)

推荐答案

小例子:

D1 中的公式:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),0,1)),--(B2:B7=1))

F1 中的公式:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),0,1)),--(B2:B7=-1))

<小时>

结果

如果根据您的评论由于错误值而无法使用上述内容，您可以做的一件事是使用一些 IF 语句构建一个值数组，如下所示:

If you can't use the above because of error values as per your comment, one thing you could do is to build an array of values with some IF statements like so:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),0,1)),IF(ISNUMBER(B2:B7),IF(B2:B7=1,1),0))

和:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),0,1)),IF(ISNUMBER(B2:B7),IF(B2:B7=-1,1),0))

两个公式都输入为数组公式！

Both formula entered as arrayformula!

这篇关于过滤器中的 Countif 1 和 -1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！