Excel Sum that calculates from the next row up

Excel Sum of from a cell to the RowAbove, or the next row above

Excel 2007 Bible
I got the idea and concept here: Three Ways to Reduce Errors In Your Excel SUM Formulas

Essentially; what I wanted to do was prevent the Insert Row just above the total line to always include the row above. So after playing a bit I found the site above.

So here is the process:
  1. In the workbook you want to have the sum click on any cell
  2. Depending on the version of Excel find where you name a range, for 2007 Formulas, Define Name
  3. A new name dialog box is displayed
  4. Make Name "RowAbove"
  5. Scope: Workbook
  6. Add a comment the next row up
  7. Delete the sheet name and enter in the row aboves address, in my case I clicked on =MySheet!$B$4, so I replace that with "=!B3"
  8. Then anywhere I want to add a sum or subtotal I just use sum(A3:RowAbove), then that will sum from A3 to the row above

Now even if a insert row above on the formula it will add it the value to the sum.


Popular Posts