Hi

Bob's formula will work fine, if you make the ranges absolute.

=SUMPRODUCT(--($A$2:$A$2000=A2),--($B$2:$B$2000=1))
If you want to suppress seeing the total on every line for the student,
then use the following.

=IF(A3=A2,"",SUMPRODUCT(--($A$2:$A$2000=A2),--($B$2:$B$2000=1)))
This will just put the value on the last line for each student.


--
Regards

Roger Govier


"crystal" wrote in message
...
I don't think I was clear in my question because the suggested formula
is
putting a zero in very cell of col C. Or I don't know how to apply
the
formula. I am rephrasing my question.

In Col A I have 2000 student ID numbers. For some of those ID numbers
I
have ten lines.

In col B I have the coded data for the student ID showing which
classes they
took. Each class has a separate line.

Also in Col B I have a digit (the number 1) which is the code for
special
training received.

I need to sub total those ones for each student id and put that sub
total
into col 3 by the first (or last) entry of the student id.

So the project starts like this: And I want to get
this
result
Col A Col B Col
C
21 ABC
21 def
21 1
1
22 fgh
22 1
1
23 1
23 1
2
26 rtr
26 str
0

I hope this clarifies my question ... or that you can help me adjust
the
formula. Thank you. Crystal Bujol
Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20="student_id"),--(B2:B20=1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"crystal" wrote in message
...
I have a worksheet with two colums of data: Col. A has the student
ID.
Col
B had codes and the integer "1." There may be 10 lines of codes
for each
student, or six lines. I need to sub total the number of "1's"
for each
student id and put that number into Col C. Can I do this with sub
totals?
How do I write the formula to print a sub total for each student id
in col
3?

thank you.

Rev. Crystal