In this chapter, you’ll learn about how to create a summary table of a student questionnaire in Microsoft Excel by using the functions. We will make use of COUNTIF, MATCH, INDIRECT and ROW functions.
Let’s take an example and understand how all these formulas will work:-
Column A contains student name, columns B to F contains the answer (Y/N) to 5 questions from each of the 5 students.
Each row includes the answers of one of the student. We want to create a summary table that will show a count of the questions answered Y and N for each student.
Follow below given steps:-
- Enter the formula in cell I2
- Press Enter
- Copy the same in the range I2:J6
As you can see in the above image according to Student ID function will count the number of replies as per the categories.
- We have used COUNTIF function to count the Y/N category numbers
- Indirect function we have used to provide the range to COUNTIF function
- Match function we have used to match the categories and student id
- Row function is also used to arrange the range value
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org