# Filtering on gathered statistics adds dummy empty rows and fails anyway

I have a simple data set:

``````Pupil A Teacher A   pass
Pupil B Teacher A   fail
Pupil B Teacher B   pass
``````

And want to compute the following statistics:

``````Teacher     pass rate
Teacher A    50%
Teacher B   100%
``````

The way I went about it is to create two columns with formulas, `=unique(B:B)` and `=COUNTIFS(C:C,"pass", B:B, E2) / COUNTIF(B:B, E2)`.

Now, this works correctly but when creating a filter and ordering by pass rate, I got this: Two-part question:

1. What am I missing? How to fix it?
2. Is there a smarter and a better way to do this?
• Could you share a(n Editable) sample sheet via link with some sample data? It'd be fairly simple to show you on a sheet, but difficult to demonstrate in the abstract – MattKing Feb 6 at 22:03
• @MattKing sure, see docs.google.com/spreadsheets/d/… the result is correct, but clicking "sort Z -> A" on pass rate breaks it :/ – g_user Feb 6 at 23:28
• Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. – marikamitsos Feb 7 at 17:26

The issue you're having the with the formula you're trying is less with the formulas and more with the Filter you've applied afterwards. for those Filters to work, you need to have "static" data. at least one column of it anyway. Whereas all your data is being supplied by formulas. A better way to get at what you're going for (i think) is this query, which i paced on your sheet in cell D1. Note that it sorts by the pass rate, (Z->A)

``````=ARRAYFORMULA(QUERY({Data!B:B,N(Data!C:C="pass")},"select Col1,AVG(Col2) where Col1<>'' group by Col1 order by AVG(Col2) desc label Col1'Teacher',AVG(Col2)'Pass Rate'",0))
``````

You could also try one of the following 2 solutions offered as well:

### Solution 01

(using a check-box)

``````=IF(B2=TRUE,SORT(QUERY({unique(Data!B2:B),ArrayFormula(IF(unique( Data!B2:B)<>"",COUNTIFS(Data!C2:C,"pass", Data!B2:B, unique(Data!B2:B)) / COUNTIF(Data!B2:B, unique(Data!B2:B)),""))}, "where Col2 is not null"),2,1),
QUERY({unique(Data!B2:B),ArrayFormula(IF(unique( Data!B2:B)<>"",COUNTIFS(Data!C2:C,"pass", Data!B2:B, unique(Data!B2:B)) / COUNTIF(Data!B2:B, unique(Data!B2:B)),""))}, "where Col2 is not null"))``````

### Solution 02

(using a dropdown in data validation)

``=IFERROR(QUERY({unique(Data!B2:B),ArrayFormula(IF(unique( Data!B2:B)<>"",COUNTIFS(Data!C2:C,"pass", Data!B2:B, unique(Data!B2:B)) / COUNTIF(Data!B2:B, unique(Data!B2:B)),""))}, "where Col2 is not null order by "&G2&" format Col2 '#%' "), "please make your selection in G2")``

(Both the check-box as well as the dropdown could be placed anywhere on the sheet)

Here is a DEMO SHEET to test it out.