HELP! Formula not working

[mathsteacher]

I am trying to create an excel sheet that I can use to analyze data from a multiple choice test. I have created a sheet, "Student Responses" that marks the exam once I enter student data and includes the special characters EX for exempt, NR for no response if left blank, and MR for multiple responses.

The test is divided into sections based on topic that I need to calculate the percentage of correct answers in each section. Each section has 5 questions. I have created my formula in the Sheet "Student Lesson Data" which calculates the percentage in my first column (topic 1), however, when I try to apply it in the second column (topic 2) it does not work. Here is what I have for my formula:

=IF(OR(@'Student Responses'!D6:H6="",@'Student Responses'!D6:H6="ex"),"",(SUMPRODUCT(--('Student Responses'!$D$4:$H$4='Student Responses'!$D6:$H6)))/5)

I have carried the formula over to the next column and adjusted the cells to match with topic 2. You can see this in cell E4 and E33. Even though the formula is exactly the same as the previous column with only a minor change, I am getting the Value# error. 

Please note that I have included the IF/OR function to consider students who are exempt or did not write the assessment.

Here is a link to the template so you can see how I have designed the sheet. 

Template.xlsx

Any help would be much appreciated.

[#2 · Accepted solution]

@mathsteacher 

In 'Student Lesson Data'!E4 you call in formula Student Responses'!I6:M6 and after that add implicit intersection '@' in front of them. That returns !Value# error, rows 6 and 4 are not intersected.

Not sure what you try to do. In brief, '@' is only for values in the same row of this or another sheet.

[#3]

@mathsteacher, I can't really understand what you were trying to do with the implicit intersection @ operator. See my version of the formula attached - using OFFSET(). Looks a bit lengthy but it is the same chunk of code all over again.

[#4]

Thanks! @Sergei Baklan 

I did not have the @in my formula initially, but when I only had the first column, it created a spill into the next column which I did not want. I have taken them out and the formula is working the way I want.