I have a 2003 spreadsheet that lists these column headers

LastName Firstname Grade NumberOfTardies

Each student has a row for each quarter, regardless of the number of
tardies the student received. So, Billy Thompson's would look like
this:

LastName Firstname Grade NumberOfTardies
Thompson Billy 8 0
Thompson Billy 8 1
Thompson Billy 8 8
Thompson Billy 8 7

What I want to do is have it total up the number of tardies per kid
per year, so in my example it would take all those totals and
ultimately look like this:

LastName Firstname Grade NumberOfTardies
Thompson Billy 8 16

Anyone know an easy way of doing that?

You can use what is known as a CSE formula (ctl+shift+enter)
Assuming the name is in column B and number of tardies is in column D

=SUM(IF(B1:B12="Billy",D1:D12))

of course you can change to anything you want, and you can click on a cell
containing Billy's name instead of typing directly. The important thing to
remember is that before you hit enter to submit the formula CSE hold control,
shift, and then hit enter. This is also called an array formula. You know you
did it right if there are {}curly braces around your formula. Once you have
this, you can copy and paste and not worry about CSE for each one.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Cytorak" wrote:

I have a 2003 spreadsheet that lists these column headers

LastName Firstname Grade NumberOfTardies

Each student has a row for each quarter, regardless of the number of
tardies the student received. So, Billy Thompson's would look like
this:

LastName Firstname Grade NumberOfTardies
Thompson Billy 8 0
Thompson Billy 8 1
Thompson Billy 8 8
Thompson Billy 8 7

What I want to do is have it total up the number of tardies per kid
per year, so in my example it would take all those totals and
ultimately look like this:

LastName Firstname Grade NumberOfTardies
Thompson Billy 8 16

Anyone know an easy way of doing that?

And another thought is to concatenate the first and last name in another cell
="firstname" & "lastname"
then sort and subtotal, at each change in this new column, sum tardies
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Cytorak" wrote:

I have a 2003 spreadsheet that lists these column headers

LastName Firstname Grade NumberOfTardies

Each student has a row for each quarter, regardless of the number of
tardies the student received. So, Billy Thompson's would look like
this:

LastName Firstname Grade NumberOfTardies
Thompson Billy 8 0
Thompson Billy 8 1
Thompson Billy 8 8
Thompson Billy 8 7

What I want to do is have it total up the number of tardies per kid
per year, so in my example it would take all those totals and
ultimately look like this:

LastName Firstname Grade NumberOfTardies
Thompson Billy 8 16

Anyone know an easy way of doing that?

You could sort the datatable by lastname/firstname and then use the subtotal
function on the data menu to sum the number of tardy values. Using the
outline buttons you could collapse and expand the data to disply Overall
totals, Yearly totals per student and all totals w/detail lines.
--
Kevin Backmann


"Cytorak" wrote:

I have a 2003 spreadsheet that lists these column headers

LastName Firstname Grade NumberOfTardies

Each student has a row for each quarter, regardless of the number of
tardies the student received. So, Billy Thompson's would look like
this:

LastName Firstname Grade NumberOfTardies
Thompson Billy 8 0
Thompson Billy 8 1
Thompson Billy 8 8
Thompson Billy 8 7

What I want to do is have it total up the number of tardies per kid
per year, so in my example it would take all those totals and
ultimately look like this:

LastName Firstname Grade NumberOfTardies
Thompson Billy 8 16

Anyone know an easy way of doing that?

To count the tardies try

=SUMPRODUCT((A2:A18="Thompson")*(B2:B18="Billy")*( D2:D18))

Mike

"Cytorak" wrote:

I have a 2003 spreadsheet that lists these column headers

LastName Firstname Grade NumberOfTardies

Each student has a row for each quarter, regardless of the number of
tardies the student received. So, Billy Thompson's would look like
this:

LastName Firstname Grade NumberOfTardies
Thompson Billy 8 0
Thompson Billy 8 1
Thompson Billy 8 8
Thompson Billy 8 7

What I want to do is have it total up the number of tardies per kid
per year, so in my example it would take all those totals and
ultimately look like this:

LastName Firstname Grade NumberOfTardies
Thompson Billy 8 16

Anyone know an easy way of doing that?