count filtered rows

[lcornaro]

Hi, I filtered my table and need to count the "Y"s in a column. The formula I have is =COUNTIF(K2:K3480;"Y") but it doesnt update if I filter the table.

 

[Richard Schollar]

If you are actually filtering on the K column (ie for value Y) and then simply need to count all the visible rows, you can use SUBTOTAL I believe:

=SUBTOTAL(3;K2:K3480)

 

[azumi]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(K2:K3480,ROW(K2:K3480)-ROW(K2),0,1)),--(K2:K3480="Y"))

Azumi

 

[azumi]

Or

=SUMPRODUCT(SUBTOTAL(3,OFFSET(K2,ROW(2:3480)-2,))*(K2:K3480="y"))

Azumi

 

[lcornaro]

thanks azumi! Worked magic!