Doing a countif formula not counting empty or hidden fields

[crlsmiro]

Im working on a spreadsheet where I need to count the occurrences of a specific number in a column (Example)
A
0
1
0
0
2

0

As I filter other columns I want to be able to know how many 0's, how many 1's and how many 2's (not including the hidden or blank ones). I know I can't use subtotal. Any help will be appreciated.









0





0

 

[T. Valko]

Im working on a spreadsheet where I need to count the occurrences of a specific number in a column (Example)
A
0
1
0
0
2

0

As I filter other columns I want to be able to know how many 0's, how many 1's and how many 2's (not including the hidden or blank ones). I know I can't use subtotal. Any help will be appreciated.









0





0

To count the number of 1s in the range B5:B20 of a filtered (or unfiltered) list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B5,ROW(B5:B20)-ROW(B5),0)),--(B5:B20=1))

 

[AD_Taylor]

Why not use SUBTOTAL? It's the only way I know how to do it. Try this and just change all of the references to A:A to either your column letter or your range such as A1:A21. Should still work.

=SUMPRODUCT(SUBTOTAL(102,OFFSET(A:A,ROW(A:A)-MIN(ROW(A:A)),0,1)),--(A:A=2))

Hope this helps!