Thread: Data analysis that has headers with subheaders

I'm using Excel 2004 (Mac) but also have access to Excel 2007(PC).
I have a list of data that has complex headers that I need to correlate the data with. Basically I have student demographic data, then I have to track how each student did on each question, but each question also corresponds to a reading standard and I need to pull two pieces of info from that. How they scored on the assessment (# of answers correct), and if they missed a certain standard twice or more (There are 4 instances of StandardA in the test and they had wrong answers on 2 or more of them). I have the solution to the first part. I entered the results of each question as a 1 for correct and 2 for incorrect then did a countif and only counted the 1s. I am stumped on getting an analysis of how each student did on the indicators. Since the indicators can occur more than once, generating a pviot table doesn't work in this instance, it'll count of 1s and 2s for StandardA, StandardA2, StandardA3, etc. and not the counts of 1s and 2 for all instances of StandardA.

Example:
StudentNum Name School Grade Question1 Question2 Question3 Question4
(Indicators ---> go under each question) A(Question1) B(Q2) C(Q3) B(Q4)
1234 Bob Sunset 3 1 1 2 1
4312 Sally Morningside 5 1 2 2 1
2342 John Sunset 3 1 2 1 2
4121 Ann Morningside 5 2 1 1 2

What I want:
For student Bob - Number of times he answered incorrectly StandardA, StandardB, StandardC, StandardD, etc.

I thought of transposing the table into a list and that helped me to sort it so that all StandardAs are together, etc. etc. Then I inserted manually a row between each standard and did a countif of only the 2s, then I transposed that data list into a table again and tried to create a pivot table from that. But that would eventually be a lot of work and the we have over 1700 students. I was hoping that I could do some kind of conditional count when the data is in this table format as above. So that it would look at the column name and if that column name equals StandardA then it would do a countif if the value is 2 for each student.

So with a data set of:
Name StandardA StandardB StandardA StandardC StandardB
Bob 2 1 2 1 2

The formula would return 2 for StandardA
and 1 for StandardB
and 0 for StandardC
etc. etc.

I've attached a small example that I hope is more clear. Any help on making the process more streamlined and elegant is appreciated.

-jw

Hi jw,

long term, a pivot table might be the best solutions to slice, dice, sum and categorise your data. But for a quick and dirty fix, try this:

In cells P2:R2 enter the text A, B, and C, one letter per cell.
in P3 put this formula

=SUMPRODUCT(--($E$2:$M$2=$P$2),--(E3:M3=2))

copy down and across. Format cells with custom format "0;;;" to hide zeros.

viola!

see attached

Great. Glad to help. Could you mark the thread solved, please. Here is how