Creating a Summary Table of a Student Questionnaire

 

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.

 

image 1

 

Follow below given steps:-

  • Enter the formula in cell I2
  • =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)

image 2

 

  • Press Enter
  • Copy the same in the range I2:J6

image 3

 

As you can see in the above image according to Student ID function will count the number of replies as per the categories.
 
Formula Explanation:-

  • 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

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

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 info@exceltip.com

 

 



Example:


Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>