#1   Report Post  
PAT D 1951
 
Posts: n/a
Default autosum problems

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?
  #2   Report Post  
Steve Smallman
 
Posts: n/a
Default

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


"PAT D 1951" <PAT D wrote in message
...
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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"Problems during load" Vincent Excel Discussion (Misc queries) 1 January 19th 05 09:38 PM
I am having problems creating pivot table of data wyman Charts and Charting in Excel 1 January 12th 05 06:17 PM
Problems opening Excel files using DFS links Byron Excel Discussion (Misc queries) 2 January 2nd 05 12:31 AM
Exel Problems! PW11111 Excel Discussion (Misc queries) 2 December 10th 04 12:34 AM
Problems with Excel formulas when 2002 upgraded to XP Kathi McGraw Excel Worksheet Functions 0 November 16th 04 06:27 PM


All times are GMT +1. The time now is 02:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"