0

I am counting the number of responses for the the different type of responses (Strongly Agree, Agree, Disagree) for each class (class 1, class 2):

Image of my problem

I am able to do a COUNTIFS for each different subject (sub 1, sub 2). Eg, for sub 1, to count the number of students from [Class 1] with responses Strongly Agree. I use the formula: 

=COUNTIFS($A$2:$A$5,$A10,$B$2:$B$5,B$9)

However, this means I need to change the reference column for each subject.

Is there a more efficient way for doing it such that if I change the subject (cell A8), eg from sub 1 to sub 2, the statistic will change accordingly?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Kin Chuah Chan
  • 3
  • 1

2 Answers2

0

You need to use MATCH to return the column number of the subject, and then use ADDRESS and INDIRECT to make it into an address for COUNTIFS to act upon. Something like this:

=COUNTIFS($A$2:$A$5, $A10, INDIRECT(ADDRESS(2, MATCH($A$8, $A$1:$C$1, 0))&":"&ADDRESS(5, MATCH($A$8, $A$1:$C$1, 0))), B$9)

Where the MATCH($A$8...) refers to the subject cell, and the ADDRESS(2.. and ADDRESS(5.. are the top and bottom rows of the list that you want to count over.

The alternative to this would be to add an additional subject column and only have a single column that you want to count over. This would only require modification of the formula you posted by adding an additional criteria.

mike7mike
  • 433
  • 1
  • 5
  • 16
  • Hi mike7mike, it works! Thanks so much. For the Address(2.. and Address(5.., how can it be used to reference to another worksheet? In my case, the name of reference worksheet is "Raw" When I tried Address (Raw!2..., excel said there is an error in formula When I tried Address (Raw!2:2...,I get [#value!] – Kin Chuah Chan Oct 13 '15 at 15:40
  • Hi @KinChuahChan, sorry for the delay in response. You need to insert the sheet name at the before the address statment i.e. INDIRECT("Raw!"&ADDRESS(... – mike7mike Oct 20 '15 at 15:14
0

You might want to consider unpivoting then constructing a new PT from the resulting table with Column above Row for ROWS and Count of Value for VALUES:

SO33098690 example

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Hi pnuts, Thanks for tidying up my question and providing the solution. Did not try it out as I am unfamiliar with pivot table. In any case, appreciate your response! – Kin Chuah Chan Oct 13 '15 at 15:48