sub totals

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  
0
Crystal (38)
4/20/2007 3:58:02 AM
excel.newusers 15348 articles. 2 followers. Follow

8 Replies
849 Views

Similar Articles

[PageSpeed] 5

=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" <crystal@discussions.microsoft.com> wrote in message 
news:78DC4D7D-A488-4D43-8C29-D7D81D02AD47@microsoft.com...
>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 


0
bob.NGs1 (1661)
4/20/2007 8:33:52 AM
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" <crystal@discussions.microsoft.com> wrote in message 
> news:78DC4D7D-A488-4D43-8C29-D7D81D02AD47@microsoft.com...
> >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 
> 
> 
> 
0
Crystal (38)
4/20/2007 3:42:03 PM
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" <crystal@discussions.microsoft.com> wrote in message 
news:FE96FF7C-B97C-4EB5-80C8-6AE3E8ECFBA3@microsoft.com...
>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" <crystal@discussions.microsoft.com> wrote in message
>> news:78DC4D7D-A488-4D43-8C29-D7D81D02AD47@microsoft.com...
>> >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
>>
>>
>> 


0
roger5293 (1125)
4/20/2007 4:02:18 PM
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" <crystal@discussions.microsoft.com> wrote in message 
> news:FE96FF7C-B97C-4EB5-80C8-6AE3E8ECFBA3@microsoft.com...
> >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" <crystal@discussions.microsoft.com> wrote in message
> >> news:78DC4D7D-A488-4D43-8C29-D7D81D02AD47@microsoft.com...
> >> >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
> >>
> >>
> >> 
> 
> 
> 
0
Crystal (38)
4/20/2007 4:38:02 PM
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" <crystal@discussions.microsoft.com> wrote in message 
news:BDCE586F-7D5A-4910-A44D-A1DBD08D6FAE@microsoft.com...
> 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" <crystal@discussions.microsoft.com> wrote in message
>> news:FE96FF7C-B97C-4EB5-80C8-6AE3E8ECFBA3@microsoft.com...
>> >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" <crystal@discussions.microsoft.com> wrote in message
>> >> news:78DC4D7D-A488-4D43-8C29-D7D81D02AD47@microsoft.com...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>> 


0
roger5293 (1125)
4/20/2007 5:02:49 PM
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" <crystal@discussions.microsoft.com> wrote in message 
> news:BDCE586F-7D5A-4910-A44D-A1DBD08D6FAE@microsoft.com...
> > 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" <crystal@discussions.microsoft.com> wrote in message
> >> news:FE96FF7C-B97C-4EB5-80C8-6AE3E8ECFBA3@microsoft.com...
> >> >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" <crystal@discussions.microsoft.com> wrote in message
> >> >> news:78DC4D7D-A488-4D43-8C29-D7D81D02AD47@microsoft.com...
> >> >> >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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Crystal (38)
4/20/2007 6:16:03 PM
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" <crystal@discussions.microsoft.com> wrote in message 
> news:BDCE586F-7D5A-4910-A44D-A1DBD08D6FAE@microsoft.com...
> > 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" <crystal@discussions.microsoft.com> wrote in message
> >> news:FE96FF7C-B97C-4EB5-80C8-6AE3E8ECFBA3@microsoft.com...
> >> >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" <crystal@discussions.microsoft.com> wrote in message
> >> >> news:78DC4D7D-A488-4D43-8C29-D7D81D02AD47@microsoft.com...
> >> >> >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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Crystal (38)
4/20/2007 6:22:03 PM
Select then cell where you want it and paste it into the formula bar instead 
of directly into the cell


-- 
Regards,

Peo Sjoblom



"crystal" <crystal@discussions.microsoft.com> wrote in message 
news:D4A5DFC9-7562-4E05-8350-1988132D7710@microsoft.com...
> 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" <crystal@discussions.microsoft.com> wrote in message
>> news:BDCE586F-7D5A-4910-A44D-A1DBD08D6FAE@microsoft.com...
>> > 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" <crystal@discussions.microsoft.com> wrote in message
>> >> news:FE96FF7C-B97C-4EB5-80C8-6AE3E8ECFBA3@microsoft.com...
>> >> >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" <crystal@discussions.microsoft.com> wrote in message
>> >> >> news:78DC4D7D-A488-4D43-8C29-D7D81D02AD47@microsoft.com...
>> >> >> >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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
terre081 (3244)
4/20/2007 8:04:24 PM
Reply:

Similar Artilces:

Duplicating MainForm, SubForm and Sub-SubForm
Hello Everyone, I have an Order Entry Form composed of a MainForm, a SubForm and 2 Sub- Subforms. The SubForm is a continuous form. The two Sub-SubForms are children of a SubForm via a foreign key (which is the Primary Key of a SubForm). There are some instances when customers revised their order by changing just the amount of orders or by just removing one item and the rest are the same. To do this, I would like to keep the initial order (record) and create another record by duplicating the first order then change the amount (or remove an item whatever the case maybe) on the new record. The...

sum total hours and minutes
ok I have read all the questions and answers and I still can't get my program to work: here is what I do: July Event StartTime End Time 8 Purchase cat food / diet pepsi / rug cleaner 1:30 PM 2:15 PM 00:45 15 Pay house gas and phone bills 10:00 AM 10:30 AM 00:30 the format I use for start time is: TIME 1:30PM the format I use for end time: TIME 2:15PM the last column formula is: =TEXT(D4-C4,"hh:mm"...

Running Total 11-23-09
Hi, I would like to be able to have a running total for the month. date sales 10/11/2009 100 11/11/2009 50 12/11/2009 10 therefor the total as of 12/12009 will be 160 and run until the end of the month and then to start from zero and begin running the total again. thanks for your help M >>therefor the total as of 12/12009 will be 160 - I believe you mean 12/1/2009. Your words do not match the math -- Your words say to start over at the begining of each month so on 12/1/2009 ...

Outlook 2000 Sub folder
Hi I am using outlook 2000 and I have a created a subfolder in my inbox however I am unable to view the folder My Shortcuts on the left hand side of the screen where all my other sub folders are located. In the navigation bar at the top of my pane there is an icon called "move to Folder" and the folder is located here. I am unable to find the location of the folder therefore unable to open the folder or move it to "My shortcuts" I am in need of your expertise. I am ok with computers and can follow instructions so please please please help. Thanking you in ...

Money 2000 and Euro wrong total
Hi evereybody I use Money 2000 Pro (vers. 8.0) and my primary currency since 2002 is Euro (I live in Italia, Eu) I have a problem since I changed for the former currency (italian lira) to actual currency (Euro)... (My older bills were in Lira so I've changed them manually to converse in Euros.) I made changes on my multiple bills to join new currency and it works fine (Now I've 2 bills [1999-2006] in Euro) but... When I see to the main page with both bills summary the total of money is wrong. For example: Bill 1 : 1000,00 Euro Bill 2 : 2000,00 Euro SubTotal (Sum of Bill 1 + Bill 2...

What are the formula for calculating ROI all dates (Total return all dates)
From http://money.mvps.org/articles/portfolio_columns.aspx, it says that dividends should be included in calculation of ROI all dates(Total return all dates) So the formula for ROI should be ((market price - purchase price)+ dividend) / purchase price. Is this correct? However, what I am seeing is that ROI all dates is calculated as (market price - purchase price)/purchase price. Is there any other parameter that includes dividends also In microsoft.public.money, rvsw@hotmail.com wrote: > From http://money.mvps.org/articles/portfolio_columns.aspx, it says >that dividends should be i...

sub-process
What is the sub-process function in the workflow? How do you set one up? Why would you use it? A sub-process is a way to call a manual rule. If you create a manual rule in workflow called Rule A, in another rule you can insert a sub-process and select the manual rule to run. This is a helpful way to create loops and if/then rules that trigger other events. -- Matt Wittemann http://icu-mscrm.blogspot.com "David M" wrote: > What is the sub-process function in the workflow? > How do you set one up? > Why would you use it? > ...

Growing Total using Autofilter in Excel?
Hello, I have a spreadsheet i am working on in Excel 2002. I have a ton of data which i am sorting to date using AutoFilter, which is working fine. when the data is not filtered i have a rolling total which adds up the whole thing like this. a b 1 100 100 2 100 200 3 150 350 but when i use the filter i see the larger totals in the list (which i understand) What i want is the same thing in a new column, but for only the current data from the view. example a b ~ 7 150 150 8 100 250 9 200 450 I have tried using sum and subtotal but it doesnt allow + example: =sum(b7+a8) and =subtotal(9,b7+a8...

Sub, Private Sub, Public Sub
I got a different lap top and when I moved my Personal.xls from one to the other some of my macros were not on the list of macros when you go to tools>macros. Although when I look in the code they are there. I figured out that the ones that were "Private Sub" macros were no working. I also figured that when I changed them to just "Sub" they worked. I do know that if "It aint broke don't fix it, cus to try is usually to fix it till it is broke." But what is the difference between "Sub", "Private Sub", & "Public ...

Cascade current view to sub-folders
Hi, Is there any way I can set a custom View setting to a set of folders (e.g. set a view setting for a high-level folder and then have it cascade to all sub-folders)? By a custom View I mean the way that items in a folder shoud look - i.e. font settings and the like. Cheers, Stu ...

(Sub)totals
Hello everybody. I have the following problem: I have an Excel file with Column B 'Quantity' and column F 'Sector'. Now i need to count all the quantities within the same sector Sorting + AutoSum isn't an option, since the file has other calculations in it as well, that also depend on a sort. There's in my Dutch version a function 'DBSUM' and a function 'TURNTABEL.FETCH' ... these are translated terms, so I don't know if they match with English versions ... but could someone please tell me how these functions work, since i can't get them right (n...

Great Plains payroll transaction entry total dollars per employee
When I enter transactions in payroll batches, I would like to see the entry window show me a dollar total (rate x hours) as I'm entering the transactions (per line) and then a dollar total per employee. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.mi...

Shift wise Totals
I have a shift 6 running after midnight, hence the productions shows on the next day, can I have the production for the same day. The shifts timings are shift 1 - 6am - 9am, shift 2 - 9:30am _12:30pm, shift3 - 1pm - 4pm, shift 4 - 5pm-8pm, shift 5 - 8:30pm:1130pm, & shift6 - 00am - 2:30am Thats the production for the day, but since shift 6 falls after midnight, all that is process falls on the next day, while actually the production is for the previous day. Subtract, logically, 2h30 ? SELECT ..., SUM( something) ,... FROM ... GROUP BY DateValue( dateTimeStamp - #02:30:00# ) Vande...

A way to create sub-categories?
I want to group contacts by category and then a sub-category for easy viewing. Apart from creating a new field for sub-category is there a way of doing this? Outlook doesn't seem to support sub-categories. Thanks Nope. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealot.com/cardoza http://www.cardozasolutions.com "Casebase" <junkmail@casebase.net> wrote in message news:%23rx5a...

Microsoft Office Doc Image Writer is still printing ghosts and it's totally freaking me out!!!!!!!
Every time i use 'Microsoft Office (2003) Doc Image Writer' (no printer at home) no printed document appears - ever!!! But when I try to reprint the document it's 'dis-embodied' ghost appears only in the save as dialogue box (even after a reboot) I think this is yet another Vista bug (Vista can't see/find existing documents) I've reported this before without cure Anyone know the name of it's predecessor (we use at work) which works perfectly - creates it's own document type albeit can't remember the extension I cannot repeat the problem. But ...

Are sub-totals able to be set to be bold automatically?
I have a large amount of sub-totals, which at the moment are not bold unless I do a manual job of making them so. Is there any way of automating this process in the sub-total procedure? You can use conditional formatting to bold the rows. There are instructions in Excel's Help files, and on the following page: http://www.contextures.com/xlCondFormat01.html Select the columns that contain the table Choose Format>Conditional Formatting From the first dropdown, choose Formula Is In the textbox, enter a formula that refers to a column in which the "Total" text appears...

Sub or Function not defined?!
In a further attempt to debug the disabled=false problem, I wrote out some enabling code in the subforms and call it from the parent. Here is the code in the subform... Public Sub UpdateFillsFormState(canEdit) QuantityField.Enabled = canEdit PriceField.Enabled = canEdit TaxField.Enabled = canEdit NetField.Enabled = canEdit If Me.RecordsetClone.recordCount > 0 And QuantityField.Enabled = True Then QuantityField.SetFocus End Sub Over in the main form I wait until I know the form is loaded and set up properly, then I do this... Public Sub SetupFillState() canEdit = isEditable() Call Update...

Sub error
I have created this macro but everytime I run it the Columns it hides is incorrect. It hides FGHIJK it should only hide GHI. Can anyone tell me why this is happening? Thank you for your help. Sub Done_Changes() ' ' Done_Changes Macro ' ' Range("G11:H50").Select Selection.Locked = True Selection.FormulaHidden = False ActiveWindow.SmallScroll Down:=42 Range("G54:H58").Select Selection.Locked = True Selection.FormulaHidden = False ActiveWindow.SmallScroll Down:=9 Range("G62:H74").Select Selection.Locked = T...

Filter by Form with sub form crash
Hi I'm having problems with filter by form causing access 2003 to shut down. Looking at the Northwind database the same thing happens. If a main form has "new additions" setting to no and has sub forms filter by form is used and the data serched on the main fom returns null ( eg some one filters Michal insted of Michael, access crashes. My own forms are linked to queries which results in not allowing "new additions" so this has the same effect in access shutting down. any adivce would be great -- with thanks Mike Mike, I just tried this in Access 2003 ...

disabling all menu and its sub menus
hi all, how to disable entire menu and its sub menus. i tried the following code it gets the Menu ID but is not disabling the menu items. HMENU hMenu; HWND hwnd = GetSafeHwnd(); hMenu = ::GetMenu(hwnd); int nCount=GetMenuItemCount(hMenu); for (int i=0;i<nCount;i++) { //get the sub menu HMENU hSubMenu=GetSubMenu(hMenu,i); //get the number of items in the submenu and disable them individually. int nSubItems=GetMenuItemCount(hSubMenu); for (int j=0;j<nSubItems;j++) { HMENU hSubMenu=GetSubMenu(hMenu,i);. int nSubItems=GetMenuItemCount(hSubMenu); EnableMenuItem(hSubMenu,G...

running a Sub in a class module from a functionin a general module
If I have a sub in the module for a form named frmActivity which contains a procedure named Sub cmd_close_form_Click() is there a way to call that Sub and run the procedure from a function in a general module? Thanks in advance, Paul "Paul" <begone@spam.com> wrote in message news:%23E1556FqKHA.5840@TK2MSFTNGP04.phx.gbl... > If I have a sub in the module for a form named frmActivity which contains > a procedure named > > Sub cmd_close_form_Click() > > is there a way to call that Sub and run the procedure from a function in...

How do I import sub.....
Ok, I have my personal contacts folder. It is called by default "contacts" I have out public contacts folder. It is called "IT contacts" How do I get the info from IT contacts into my personal contacts. I tried exporting a personal file, then importing it into my personal contacts, and it did, in a subfolder. I don't want it in a subfolder (my pda won't read it) I want it added to my personal contacts folder... make sense? Please help... jojo (Outlook 2000) Ctrl+drag and drop the individual items. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook ...

sorting cells in a sub routine
I'm trying to Sort a block of cells as a step in a routine. The size of the block varies, but it will always be Sorted in Ascending order by the last column. The routine works by having Excel evaluate the CurrentRegion of the ActiveCell with the CurrentRegion cells surrounding it. If they match, the sort fires. All of this works fine except when the sheet is protected. As far as I can tell, CurrentRegion causes an error message when the sheet is protected. How else can I establish the area to be sorted? I'm currently doing it by finding the first and last Columns in the Region an...

Excluding multiple checking accounts from budget totals?
Howdy! Running Money06, and I have two checking accounts synching through Bank of America. Everything there is working well, but one thing that I dont like is that the totals for BOTH accounts are added together. I have two accounts, 'personal' and 'class', and both accounts are shown in the net balance statements, the 'spending by catagory' chart on the home page etc. I would like to keep synched with my class account, but want it excluded from all of the balances.. any suggestions? "Raichean" <Raichean@discussions.microsoft.com> wrote in mes...

Sync all sub folders from a specific public folder
Hi, I know how to sync a single public folder, but is there an easier way to sync a public folder and include all levels of sub directiores related? Thanks in advance Katrin Katrin wrote: > Hi, > > I know how to sync a single public folder, but is there an easier way > to sync a public folder and include all levels of sub directiores > related? > > Thanks in advance > > Katrin You have to drag all folders to favorites in order to sync them to offline files. Subfolders too. Depending on your v. of Outlook you may be able to easily select all PF favorites for s...