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).
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
