» Counting the Values that Match Criteria in a Filtered List

Problem:

Column B shows the answers to the question numbers displayed in column A.
An auto-filter is set for both columns.
We want to create a formula that will count the number of times each possible answer (listed in cells A26:A29) occurs for each of the filtered question numbers.

Solution:

Use the SUMPRODUCT, SUBTOTAL, OFFSET, ROW, and MIN functions as shown in the following formula:
=SUMPRODUCT(($B$2:$B$22=A26)*(SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$22)-MIN(ROW($B$2:$B$22)),,))))
Enter the above formula next to the 4 possible answers (cells B26:B29).