Countif with subtotal

[iuwilliams]

I have a set up data where I need to filter by teacher and get a count of scores above a certain threshold. Please help!
I use the following formula for school data
=COUNTIF(A2:A201,">9")
I need to be able to get that same count when I filter by teacher A or B. COUNTIF is not allowed with SUBTOTAL.


Teacher skill 1 (4)Rhyme Production skill 2 (4) Isolate Initial Phonemes skill 3 (4) Identifying Final Sounds in Words Total
Teacher A 3 4 2 9
Teacher A 4 4 3 11
Teacher A 4 3 3 10
Teacher A 0 3 0 3
Teacher A 2 4 4 10
Teacher A 2 3 0 5
Teacher B 0 0 0 0
Teacher B 0 0 0 0
Teacher B 0 4 4 8
Teacher B 3 3 3 9
Teacher B 4 4 4 12
Teacher B 3 4 4 11
Teacher B 0 3 3 6

 

[Aladin Akyurek]

Try something like:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A201)-ROW(A2),0,1),--(A2:A201="teacher A"),--(E2:E201>9))

Adjust the ranges and the conditions to suit.