COUNTIF for AutoFiltered data

[Ferdinand]

I am using the subtotal function to analyze data that I AutoFilter.
This works great for AVERAGE [ subtotal(1, A1:A500) ] or COUNT [ SUBTOTAL(2, A1:A500) ] but does not work for COUNTIF since is no function_num for COUNTIF, ie. SUBTOTAL (“x”, A1:A500), since COUNTIF needs a criteria…

Any easy solution to this?

 

[Mark W.]

Suppose A1:C10 contains...

{"Field1","Field2","Field3"
;"B",2,"cat"
;"A",5,"cat"
;"C",2,"bird"
;"C",4,"dog"
;"B",2,"cat"
;"A",5,"bird"
;"A",4,"dog"
;"C",1,"cat"
;"B",4,"cat"}

If you want to count the number of {"B",2,"cat"} combinations use =SUBTOTAL(3,A2:A10) along with an Advanced AutoFilter with a criteria range containing...

{"Field1","Field2","Field3"
;"B",2,"cat"}