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