Q: Is it possible to have a SUM function automatically include all rows inserted immediately above the row of the SUM function?
A: The easiest way to achieve this is via a range name. Let’s assume there are numbers in the range A1:A10. We want a formula in cell A11 that will include any rows inserted above row 11 and below row 10. If you don’t want to use a range name then this formula will work for A11. =SUM(A1:OFFSET(A11,-1,0)) The problem with this technique is that you have to create a different formula every time you use it. The range name solution is easier to use and can be applied throughout the file, without having to use the OFFSET function. You first need to create a range name that is both dynamic and relative to the cell it is used in. Select cell A11. In Excel 2003 and earlier versions – hold the Ctrl key and press the F3 function key. In Excel 2007 and later versions – on the Formula Ribbon tab click the Define Name button next to the Name Manager. In all versions – in the Name: box type CellAbove; in the Refers to: box type =!A10; click OK. Wherever you use the CellAbove range name it will always refer to the cell directly above it. The name can be used throughout the file. In cell A11 you can now use the formula =SUM(A1:CellAbove) This formula includes any rows inserted between row 10 and row 11. This can also be applied to summing to the left. If there were numbers in cells A1 to D1 and you wanted to SUM them in cell E1 you would select E1 and create a Name CellLeft with the Refers to: =!D1 and use this formula in cell E1 =SUM(A1:CellLeft) This will include all columns inserted between columns D and E.