autosum problems

[PAT D 1951]

I am doing statistics for a sports league. I have each player sorted by last
name, then 1st name, then year played.
At the end of a season, I copy & paste each player's stat line from a
separate workbook into the end of my main file.
Each player has a TOTALS line which is determined by using AUTOSUM for each
column necessary. When I try to sort the page by the criteria I mentioned
above,
the AUTOSUM results do not include all years played.
For example, ROW 1 thru 5 is SMITH, JOE. The years are 1999, 2000, 2001,
2002, 2003. The AUTOSUM results in each column is "=sum(row1:row5).
When I add year 2004 & try to sort , the result of AUTOSUM in each column is
"=sum(row2:row6)". It should be "=sum(row1:row6).
How can I force AUTOSUM (or any SUM function) to include a SORTED row in its
calculations?

 

[Steve Smallman]

Pat,

Autosum is a quick way to add a formula that is (usually) the SUM function.
SUM takes parameters which are a range.

When you alter the range of data, you need to alter the range of the
parameters.

I see three options for you.
You could concatenate LASTNAME and FIRSTNAME to a LASTNAME, FIRSTNAME
string, and then use the Data/Subtotal function. Generally, this would add a
total below the data so that Smith, Joe would be totalled, and then SMith,
Kevin's data would be listed then SMith, Kevin's totals etc.

Alternatively, if you want your totals below the data, you could copy the
concatenated name string below the data table, and use SUMIF

The third alternative is probably the easiest for long term maintenance, but
a little more difficult conceptually. Use a Pivot Table to summarise your
data. Depending on the stats you are collecting this can get quite
complicated to set up, but can give you an automatic summary which is neat,
and quite effective.

Steve