Mix countif with subtotal

[ExcelFailure]

Morning,

I need to run a countif on a filtered column - how do I do this please?

Thanks

[benishiryo]

hi there. go to the link in my signature "Tips & Tutorials I Compiled" & download the file. Sheet 10 would be roughly what you want. you probably don't need multiplying though. so using that eg. say you want to do a COUNTIF of those where column B equals Range 2, then:
=COUNTIF(B8:B19,"Range 1")

mixing it with Autofilter would be:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B8,ROW(B8:B19)-ROW(B8),))*(B8:B19="Range 1"))

[ExcelFailure]

OK thanks. I don't have an equivalent of your column B though, because there is about 700 lines with some of them filtered out, so I just want to count the one's that are not filtered out?

[rishikrsaw]

pls share your excel sheet here

[ExcelFailure]

Well there's a slight problem there because it's an updating query table which will bug if it's not connected to our server. Let me create a sample file

[ExcelFailure]

Here attached