Subtotals (and autosums) don't change when you add a new row at the bottom of
the range. In both cases, the Excel function will generate a formula for
you, with a fixed range. If you add rows in the middle of the range, you get
automatic recalculation, but if you add a row at the bottom of the range, the
range doesn't expand. If you are subtotalling a table, you usually want to
add new rows at the bottom of each group, but to get the subtotals to work in
this case, you need to run the subtotal function again. And autosums work
the same way.

Excel has behaved this way for many versions, and I would have hoped it
would be fixed by now, but no !

Do I assume correctly from your statement "Excel has behaved this way for
many versions, and I would have hoped it would be fixed by now" that you have
Excel 2007.

If so, then with tables instead of just adding a row and inserting the
subtotal formulas in the last row, do the following:-
Right click in the table
Select Table - Totals row
An additional row will be added with the word Totals in the left cell.
Click in the cell and then click the dropdown and select the type of
subtotal you require.
Check out the formula and you will see the column number instead of a set
range and you can add rows by selecting the totals row and the rows are added
above and the formula is self correcting.
You can have separate tables for each group to be subtotalled with a grand
total outside the tables at the bottom. Adding rows in the tables auto
corrects both the subtotals and the grand total.

When using subtotals with Autofilter on a worksheet, I always try to leave a
few lines at the top to accommodate for the subtotal formulas and have the
column headers about row 6. I then freeze the pane from below the column
headers. This has a two fold advantage. The formulas can be set up to work to
the bottom of the worksheet (or at least far enough down to accommodate any
extra data likely to be added) without circular reference problems. The other
is that the totals are always visible irrespective of the scrolling of the
worksheet.

Hope this helps a little.

--
Regards,

OssieMac

Thnaks, OssieMac. Two good workarounds, but not a solution. Microsoft needs
to fix the problem, and surely it can't be that difficult !

"OssieMac" wrote:

Do I assume correctly from your statement "Excel has behaved this way for
many versions, and I would have hoped it would be fixed by now" that you have
Excel 2007.

If so, then with tables instead of just adding a row and inserting the
subtotal formulas in the last row, do the following:-
Right click in the table
Select Table - Totals row
An additional row will be added with the word Totals in the left cell.
Click in the cell and then click the dropdown and select the type of
subtotal you require.
Check out the formula and you will see the column number instead of a set
range and you can add rows by selecting the totals row and the rows are added
above and the formula is self correcting.
You can have separate tables for each group to be subtotalled with a grand
total outside the tables at the bottom. Adding rows in the tables auto
corrects both the subtotals and the grand total.

When using subtotals with Autofilter on a worksheet, I always try to leave a
few lines at the top to accommodate for the subtotal formulas and have the
column headers about row 6. I then freeze the pane from below the column
headers. This has a two fold advantage. The formulas can be set up to work to
the bottom of the worksheet (or at least far enough down to accommodate any
extra data likely to be added) without circular reference problems. The other
is that the totals are always visible irrespective of the scrolling of the
worksheet.

Hope this helps a little.

--
Regards,

OssieMac