Countif layering

I have a data file of three columns: one is a subject (math, art, etc.), the
second contains a code for the first student to get it right (AA, e.g.) and
the students that got it wrong (AA, e.g.). My data looks something like this,
but much longer:
A B C
Math AA AE
Sci AB none
Lit BE BA
Art BC AA, AC
Math AD AE

I want to compute the number of right and wrong for each cubject for each
student, in a sheet like this one:
Math R Math W Art R Art W.....
AA x y
AB
AC
AD
BA

What is the best way to calculate these numbers? What combination of
functions should I use for x and y? Thanks in advance.

With multiple entries in a single cell in columns B & C, you've got a mess to
deal with. If the row you have shown as

Art BC AA, AC

was broken into 2 rows

Art BC AA
Art AC

and the same for all other rows where you have multiple codes in a single
cell, you could get your desired results VERY, VERY EASILY with pivot table.

Short of that, I'm not sure how you could get from where you are to an
accurate result like what you want.


"Ben Blair" wrote:

> I have a data file of three columns: one is a subject (math, art, etc.), the
> second contains a code for the first student to get it right (AA, e.g.) and
> the students that got it wrong (AA, e.g.). My data looks something like this,
> but much longer:
> A B C
> Math AA AE
> Sci AB none
> Lit BE BA
> Art BC AA, AC
> Math AD AE
>
> I want to compute the number of right and wrong for each cubject for each
> student, in a sheet like this one:
> Math R Math W Art R Art W.....
> AA x y
> AB
> AC
> AD
> BA
>
> What is the best way to calculate these numbers? What combination of
> functions should I use for x and y? Thanks in advance.
>

The right answers ones are pretty easy

if your totals table is in sheet2 Manes in Column A and courses in the first
row
Assuminc Column B is Math R
enter in B2
=sumproduct(--(Sheet1!$B$1:$B$1000=$A2),--(Sheet1!$A$1:$A$1000="Math"))
Copy to the Art R and Lit R columns changing "Math" to "Art" and "Lit" as
appropriate.
Copy this down to the end of your student list

since there can be multiples in the Wrong column this equaiton gets a little
more complex
If you can change the wrong column into separate cells for each wrong person
Look at Data-Text to Columns with Comma delimiter for one method
enter in the cell under the Math W cell
=sumproduct( --(Sheet1!$c$1:$c$1000=$A2)
--(Sheet1!$D$1:$D$1000=$A2)--(Sheet1!$E$1:$E$1000=$A2),--(Sheet1!$A$1:$A$1000="Math"))
*Add as many sections in first part as is needed.*
or
=sumproduct(--(if(iserror(find($A2,
Sheet1!,$C$2:$C$1000),0,1),--(Sheet1!$A$1:$A$1000="Math"))
Copy these to the LIT W and Art W columns Making appropriate changes and
copy for your student list.



"Ben Blair" wrote:

> I have a data file of three columns: one is a subject (math, art, etc.), the
> second contains a code for the first student to get it right (AA, e.g.) and
> the students that got it wrong (AA, e.g.). My data looks something like this,
> but much longer:
> A B C
> Math AA AE
> Sci AB none
> Lit BE BA
> Art BC AA, AC
> Math AD AE
>
> I want to compute the number of right and wrong for each cubject for each
> student, in a sheet like this one:
> Math R Math W Art R Art W.....
> AA x y
> AB
> AC
> AD
> BA
>
> What is the best way to calculate these numbers? What combination of
> functions should I use for x and y? Thanks in advance.
>