Equivalent of =SUM(ABOVE) in numbers

ms officenumbers

I have a table like this,

enter image description here

I need to sum all fields above (exclude the header), the following formula works:

=SUM(C1:C5)

When I add more rows I must rewrite the formula, then I tried something like this:

=SUM(C)

Because there's a header in column C, SUM function failed as expected.

What should I use? Number does not support =SUM(ABOVE) and MS Excel is as stuck as it can be.

Best Answer

When putting a sum-over-the-entire-column formula into a regular cell, you are basically self-referencing the formula cell as a value cell.

Try putting the the formula into a footer row. This way you can reference only the cells in a column without including the header or footer row.

  1. Add a footer row in the "Header & Footer" section in the Format > Table sidebar

Numbers Table Footer Setup

  1. Put the SUM formula in the footer row (the last row in the image below) and select the entire column by referencing the column letter or the column header title (column header title here is "Header" :).

(Select footer cell, type "=SUM", press Enter, type header title or column letter, press Enter)

Numbers SUM formula footer row

As shown, only the cells, without the header or footer row, are selected. The sum is calculated over all cells in the column, even when adding new rows.

Related Question