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

=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

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

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

Thank you for your help. I pasted the formula into Col C. I did get the
desired results for the first student. But the other 1900 students are black
in Col C. Is this due to the absolute values? Am I supposed to change those
values for each student ID group? I know there is a way this can be done,
I'm an old lotus 1,2,3 user, but I've forgotten everything I knew from those
days ... and my mind has gotten older, too! All this to say, please continue
to be patient with me. Thanks.

"Roger Govier" wrote:

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

Hi Crystal

With your sample of data, I get 1 for student 21, 1 for 22, 2 for 23 and
0 for 26.

Are your student ID's text or numeric?
Are the 1's in column B text or numeric?
Is the data consistent, or have you got mixed entries?

Try changing the formula to the following, which would force them all to
be treated as text.
=IF(A3=A2,"",SUMPRODUCT(--(TEXT($A$2:$A$2000,"0000")=TEXT(A2,"0000")),
--(TEXT($B$2:$B$2000,"0")="1")))

Does that work?

--
Regards

Roger Govier


"crystal" wrote in message
...
Thank you for your help. I pasted the formula into Col C. I did get
the
desired results for the first student. But the other 1900 students
are black
in Col C. Is this due to the absolute values? Am I supposed to
change those
values for each student ID group? I know there is a way this can be
done,
I'm an old lotus 1,2,3 user, but I've forgotten everything I knew from
those
days ... and my mind has gotten older, too! All this to say, please
continue
to be patient with me. Thanks.

"Roger Govier" wrote:

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