» Creating a Summary Table of a Student Questionnaire

Problem:

Columns B:F contain the answers (Y/N) to 5 questions from each of 5 students.
Each row includes the answers of one student.
We want to create a summary table that will show a count of the questions answered Y and N for each student.

Solution:

With Student ID in cells H2:H6 and the answer criteria in cells I1:J1, use the COUNTIF, INDIRECT, MATCH, and ROW functions as shown in the following formula:
=COUNTIF(INDIRECT("B"&MATCH($H2,$A$2:$A$6)+ROW($A$2)-1&":F"&MATCH($H2,$A$2:$A$6)+ROW($A$2)-1),I$1)