I'm an Army instructor and I'm trying to build a spreadsheet that will track which instructor taught which lecture, how many questions were asked about this lecture on the four written tests, and how many of those questions were missed by a high percentage of students. I've attached screenshots of my preliminary work.
rollup.jpg
matrix.jpg
I have used the SUMIF formula shown to figure out the number of questions based on the lectures that were taught, but sometimes the instructors move to different classrooms and teach, so I need to figure out how that will work across the range shown in the second screenshot. For example, Jackson teaches a lecture in team 5, how would I word the formula that it will count that lecture and add the questions? I've tried putting the whole range of cells in the formula, but it returns #VALUE.
The other issue I have is dealing with the test versions, which have differing numbers of questions for different lectures. How do I write the formula where I can put version A,B, or C in the appropriate cells on the first screenshot and it will do the appropriate sum for the number of questions? I've tried doing a IF with nested SUMIF formulas, but it also returns #VALUE.

Any help would be appreciated.