sub totals

[crystal]

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

 

[Bob Phillips]

=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]

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

 

[Roger Govier]

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.

 

[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]

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?

 

[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.

 

[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?

 

[Peo Sjoblom]

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