Hi - probably a basic question but I'm finding it a bit annoying.
When I have column A populated with numbers and a sum total in the last row (A11) e.g. =sum(A2:A10), when I insert a row (so that the sum formula is now in A12 and the range I want to be summed is A2:A11) and add subsequent data, the total doesn't update and I have to manually edit the formula to include the additional row.
Is there a setting somewhere to do this automatically?
Thanks
Keith
Hi Keith,
The "Numbers" way would be, instead of "Excel-style" referencing a range like A2:A10, to do this:
Make sure the first row is defined as a Header Row (usually this is by default and you will see shading).
Define the last row as a Footer Row (go to row number at left and choose 'Convert to Footer' row from the dropdown menu that appears).
Use =SUM(A) as the formula in a cell in the Footer Row. The formula will sum only numbers in the "body" of the table, ignoring the Header and Footer Row(s). It will automatically include cells in subsequently added rows in the body of the table. Note how easy it is to add new body rows. Select any cell in the last body row before the Footer Row and hit 'return.'
You can also place the formula in a separate "summary" table, instead of in a Footer Row.
SG
Hi Keith,
SGIII's suggestion to define header and footer rows, then use whole column references to include all body cells in a column provides the cleanest solution. Here's an alternate method that will also do the job.
Keep an extra row in the range to be summed.
When adding a new row, click on any cell in that 'empty' row, then press option-up arrow to insert an new row above that one. The new row, inserted within the range defined in the formula will be included in the formula range, and the range will be adjusted to keep the last (empty) row as the last row of the range.
The new row does not need to be inserted in this specific position. The formula will be automatically adjusted if the new row is inserted anywhere within the currently defined range of cells.
Regards,
Barry
And check out the 'Party Planner' template (and others) at File > New in your menu to see an example of the Footer Row approach in action.
SG
column sum updates when adding rows
