CODEWITHSUNDEEP
excelexcel-formula
Sameer Farooqui
Sameer Farooqui

Reputation: 117

Countif 1 and -1 in filter

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

enter image description here

formula : 

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

formula : 

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

Upvotes: 2

Views: 84

Answers (2)

JvdV
JvdV

Reputation: 75900

Small example:

enter image description here

Formula in D1:

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

Formula in F1:

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

Result

enter image description here

enter image description here

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))

and:

=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!

Upvotes: 2

Mark S.
Mark S.

Reputation: 2596

=SUMPRODUCT(SUBTOTAL(102,OFFSET(B2:B11,ROW(B2:B11)-MIN(ROW(B2:B11)),,1))*(B2:B11=1)) to find count of 1's

=SUMPRODUCT(SUBTOTAL(102,OFFSET(B2:B11,ROW(B2:B11)-MIN(ROW(B2:B11)),,1))*(B2:B11=-1)) to find count of -1's

Just lock the cells as necessary and you're good to go.

Upvotes: 1

Related Questions