PowerPivot Help: Organizing Data using a Filter

Capture.PNG

Hi, I'm an Excel beginner that needs help with a project. As you can see, I have a model with "student names" (aa bb, cc dd ... kk ll) in rows and "questions" (q1, q2 ... q6) in columns. Randomly assigned to each question is a category (1, 2, 3). The values 1 and 0 indicate whether the student answered the question correctly.

Basically, I need to use PowerPivot to find the breakdown of how each student did on each category of questions on a much larger scale (45 questions, 30 students, categories within categories). In Excel, I was able to do this, using sumif statements, but I'm having trouble translating it into PowerPivot so that it is automated. The major problem that I keep coming across is that, from what I can tell, PowerPivot can only find sums in columns, but equations can only be inputted into columns as well.

Any help would be greatly appreciated. Thanks for your time.

Wow, that was really great, but sumif isn't a valid function in PowerPivot. Is there a way to do this so that it can translate to PowerPivot, perhaps by using a "calculate" function? Thanks for your help; it gave me a new insight to this problem.

Bumping for Visibility