sub totals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

crystal - 20 Apr 2007 04:58 GMT

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

Reply to this Message

Bob Phillips - 20 Apr 2007 09:33 GMT

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

Signature

HTH

Bob

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

>I have a worksheet with two colums of data: Col. A has the student ID.
>Col
[quoted text clipped - 7 lines]
>
> Rev. Crystal

Reply to this Message

crystal - 20 Apr 2007 16:42 GMT

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

> =SUMPRODUCT(--(A2:A20="student_id"),--(B2:B20=1))
>
[quoted text clipped - 9 lines]
> >
> > Rev. Crystal

Reply to this Message

Roger Govier - 20 Apr 2007 17:02 GMT

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.

Signature

Regards

Roger Govier

>I don't think I was clear in my question because the suggested formula
>is
[quoted text clipped - 59 lines]
>> >
>> > Rev. Crystal

Reply to this Message

crystal - 20 Apr 2007 17:38 GMT

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.

> Hi
>
[quoted text clipped - 70 lines]
> >> >
> >> > Rev. Crystal

Reply to this Message

Roger Govier - 20 Apr 2007 18:02 GMT

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?

Signature

Regards

Roger Govier

> Thank you for your help. I pasted the formula into Col C. I did get
> the
[quoted text clipped - 93 lines]
>> >> >
>> >> > Rev. Crystal

Reply to this Message

crystal - 20 Apr 2007 19:16 GMT

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.

> Hi Crystal
>
[quoted text clipped - 109 lines]
> >> >> >
> >> >> > Rev. Crystal

Reply to this Message

crystal - 20 Apr 2007 19:22 GMT

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?

> Hi Crystal
>
[quoted text clipped - 109 lines]
> >> >> >
> >> >> > Rev. Crystal

Reply to this Message

Peo Sjoblom - 20 Apr 2007 21:04 GMT

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

Signature

Regards,

Peo Sjoblom

> In your formula:
> =IF(A3=A2,"",SUMPRODUCT(--(TEXT($A$2:$A$2000,"0000")=TEXT(A2,"0000")),
[quoted text clipped - 119 lines]
>> >> >> >
>> >> >> > Rev. Crystal

Reply to this Message