Mix countif with subtotal

**ExcelFailure** · 01-06-2014, 03:32 AM

Morning,

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

Thanks

**benishiryo** · 01-06-2014, 03:38 AM

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** · 01-06-2014, 03:47 AM

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** · 01-06-2014, 03:50 AM

pls share your excel sheet here

**ExcelFailure** · 01-06-2014, 04:00 AM

Here attached

**ExcelFailure** · 01-06-2014, 03:55 AM

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