Advanced Count functions
 

I have a list of three important data. In column A I have a subject (math,
science, etc.), in column B I have a code for the one student who got it
right, and in column c I have the codes of any students who guessed wrong. I
want to create a list at the bottom of each individual students right and
wrong in each subject (i.e., Bob had 14 right in math, 8 wrong in math, 6
right in science... etc.). What combination of functions can I use to count
by means of the subject and the student's two-digit code? Thanks.

If there is a subject for all codes like

A B C
math code1 code2
math code3
math

etc. then you can use

=SUMPRDUCT(--(A2:A200="math"),--(B2:B200="studentcode1"))

count passed tests for student1, replace the criteria with cell references
and type the criteria there will avoid editing of the formula, change to
C2:C200 (whatever range you are using) and count failed tests


Regards,

Peo Sjoblom

"Ben Blair" wrote:

I have a list of three important data. In column A I have a subject (math,
science, etc.), in column B I have a code for the one student who got it
right, and in column c I have the codes of any students who guessed wrong. I
want to create a list at the bottom of each individual students right and
wrong in each subject (i.e., Bob had 14 right in math, 8 wrong in math, 6
right in science... etc.). What combination of functions can I use to count
by means of the subject and the student's two-digit code? Thanks.

If by chance your data looks like this

A B C
math code1 code 2, code 3
math code 1 code 3, code 2

then use

=SUMPRODUCT(--(A2:A200="math"),--(ISNUMBER(FIND("code 1",C2;C200))))

for the failures (Peo's still applies to passes)


I offer this because of the way I read this statement

in column B I have a code for the one student who got it
right, and in column c I have the codes of any students who guessed wrong
--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peo Sjoblom" wrote in message
...
If there is a subject for all codes like

A B C
math code1 code2
math code3
math

etc. then you can use

=SUMPRDUCT(--(A2:A200="math"),--(B2:B200="studentcode1"))

count passed tests for student1, replace the criteria with cell references
and type the criteria there will avoid editing of the formula, change to
C2:C200 (whatever range you are using) and count failed tests


Regards,

Peo Sjoblom

"Ben Blair" wrote:

I have a list of three important data. In column A I have a subject
(math,
science, etc.), in column B I have a code for the one student who got it
right, and in column c I have the codes of any students who guessed
wrong. I
want to create a list at the bottom of each individual students right
and
wrong in each subject (i.e., Bob had 14 right in math, 8 wrong in math,
6
right in science... etc.). What combination of functions can I use to
count
by means of the subject and the student's two-digit code? Thanks.

I'm a bit confused. Let's suppose my data looks like this:
A B C
Math AA AD
Lit AA AC
Art AB AE

The results will be in a table like this:
code Math R Math W
AA x y
AB
AC

What exact formula belongs in x or y?

as Peo gave you

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ben Blair" wrote in message
...
I'm a bit confused. Let's suppose my data looks like this:
A B C
Math AA AD
Lit AA AC
Art AB AE

The results will be in a table like this:
code Math R Math W
AA x y
AB
AC

What exact formula belongs in x or y?