# Equivalent of =SUM(ABOVE) in numbers?

I have a table like this,

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.

• You said, "When I add more rows I must rewrite the formula", where are you adding the rows, before the cell that has the formula or after? If before, the formula should automatically update. If after, and I were doing it, I'd remove the formula, reselect the cells to sum and then click Insert > Formula > Sum or drag and drop the (SUM n) from the bottom of the window and place it in whatever cell you want. If I'd also use SUM often enough then I'd gave it a keyboard shortcut (^S) in: System Preferences > Keyboard > Shortcuts > App Shortcuts – user3439894 Sep 13 '19 at 3:14
• @user3439894 Yes, I'm adding rows after the last row, so that's the problem – daisy Sep 13 '19 at 3:17

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

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)

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.

• Yes, this one excludes the header automatically – daisy Sep 14 '19 at 9:59

Using version 5.1 (5683), to my (limited) knowledge of Numbers, there is not an equivalent of the Excel `=SUM(ABOVE)` formula in Numbers.

If you primarily want to use the keyboard, start by adding a keyboard shortcut for the SUM formula in Numbers in System Preferences > Keyboard > Shortcuts > App Shortcuts as shown in the image further below.

I gave it: ^S

Now, in Numbers, you can sum any column above the selected cell by pressing: ^S

If you then need to extend the number of cells beyond the cell that has the SUM formula, simple do the following:

1. Select the cell that contains the SUM formula and press the delete key.
2. Fill in the values of the empty cells as needed.
3. Press: ^S

You now have a new sum for all cells above the selected cell you want the SUM formula in.

The shortcut is: Control-S

• This is exactly what I'm doing. Fixed range of rows to sum up. When more rows like B12, B13 is added, you will need to edit the formula to reflect the changes. In excel `sum(above)` can dynamically select all rows above, excluding the header – daisy Sep 13 '19 at 3:16