Countif divided by count with filter

**btdthtbm** · 03-19-2013, 10:01 PM

Hello, I need assistance with a countif/count with a filter. I have searched and found part of the answer but it doesn't seem to be working for me. I am looking to get a percentage of accurracy for the period(s) that are filtered.

This is my initial formula which works but not after the filter which is by period in column A.
=(COUNTIF(G5:G21,1)/COUNT(G5:G21))

After adding the sumproduct that I came across I don't get the results I am expecting. Most of the threads I have read are looking for a string character and not a number, not sure if that is why it is not working or not.

This is one of many variations of formulas I found and tried to make work in my sheet by changing the cell references. I guess I am missing something, maybe just a DUH moment. I did try to make the "1" be 1 and that gave me 16 without filter which should actually be 1 or if percentage 100 when it is "1" it gives me 0.
=SUMPRODUCT(SUBTOTAL(3,OFFSET(G5:G21,ROW(G5:G21)-MIN(ROW(G5:G21)),,1))*(G5:G21="1"))

Any help is appreciated and thank you for your time.
Krise

**JBeaucaire** · 03-19-2013, 10:11 PM

Care to post a small workbook showing some sample data, and manual mockups of the results you're trying to formulate after applying a filter?

Click GO ADVANCED and use the paperclip icon to post up your sample workbook.

**benishiryo** · 03-19-2013, 10:13 PM

hi Krise, welcome to the forum. when you put double quotes for "1", you are looking for a text. your formula gives you 16 because you only did the COUNTIF portion. you still have to divide by the COUNT. so:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(G5,ROW(G5:G21)-MIN(ROW(G5:G21)),,1))*(G5:G21=1))/SUBTOTAL(2,G5:G21)