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? |
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? |
All times are GMT +1. The time now is 06:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com