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

Dear Roger

You ask:

Are your student ID's text or numeric? -- THEY ARE NUMERIC
Are the 1's in column B text or numeric? THE 1's ARE NUMERIC
Is the data consistent, or have you got mixed entries? THE DATA IS MIXED WITH 1's AND OTHER TEXT.

Also, my son, who is old, too, just mentioned something about doing the sub
totals in the printer options. I looked there and vaguely remember something
similar to the pivotal table and sub totals. Just wondering if that would be
easier to get one student id to print with the total. Well, while I wait for
your response, I thank you and Bob for your help.

"Roger Govier" wrote:

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

In your formula:
=IF(A3=A2,"",SUMPRODUCT(--(TEXT($A$2:$A$2000,"0000")=TEXT(A2,"0000")),
--(TEXT($B$2:$B$2000,"0")="1")))

When I try to paste it to a cell, it takes up two cells and I get a message
that it contains a missing "(" ... am I supposed to type in the two dases in
from of (Text?

"Roger Govier" wrote:

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

Select then cell where you want it and paste it into the formula bar instead
of directly into the cell


--
Regards,

Peo Sjoblom



"crystal" wrote in message
...
In your formula:
=IF(A3=A2,"",SUMPRODUCT(--(TEXT($A$2:$A$2000,"0000")=TEXT(A2,"0000")),
--(TEXT($B$2:$B$2000,"0")="1")))

When I try to paste it to a cell, it takes up two cells and I get a
message
that it contains a missing "(" ... am I supposed to type in the two dases
in
from of (Text?

"Roger Govier" wrote:

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