You don't mention what, if anything, happens to the "D283". will the formula become
in other words, is there always a three-cell distance between those?
Also, an example workbook would be helpful, to visualize why D280 is "not in the total row".
Before attempting to use VBA (which seems overly-complicated), can you try any of the following?
- Convert the D column to a table, then use table references, which are relative. Bonus: the total updates automatically
- Use the SUM function, with an arbitrarily large ending, SUM(D1:D999)
- A combination of COUNT and SUM functions, for subtraction and other totals.
If VBA is needed, would you want the total triggered each time the sheet changes, or would you prefer to click a button to refresh all totals at once?