Automatic updates of calculations when filters are applied,updated

[kcompton]

Hello
How would I create a formula that updates automatically when I update/change the selections in my spreadsheet filters?
I have a large spreadsheet with columns for school names, teacher names, class names, subjects, students and columns for test scores.
I need to count the number of test scores that are not blank and that meet certain criteria (i.e test score = "Yes")
When I filter for the school, teacher, class name and subject, I need for the formula at the bottom of the spreadsheet's column for count of test scores to update so that the count of the test scores meeting the criteria for "Yes" is only being counted for the filtered records, not for the entire column.
The formula I have at the bottom of the Test Score column is =COUNTIF(AM1:AM8181,"*Yes*") - this gives me the count for the entire unfiltered spreadsheet.
When I filter for just one specific school, one specific teacher, one specific class and one specific subject, the COUNTIF total for the filter does not change.
I need it to update automatically. Excl's workbook calculation option is set to automatic.

Thank you in advance for any guidance...