过滤器中的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))

---

结果

---

Result

如果由于注释中的错误值而不能使用上面的代码，那么您可以做的一件事就是用一些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))

两个公式都输入为arrayformula！

Both formula entered as arrayformula!

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