autosum problems

P

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top