1

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.

Improve this question
2
  • 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
3

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.

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

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.

App Keyboard Shortcut

The shortcut is: Control-S

Improve this answer
0

SUM(INDIRECT(ADDRESS(2,COLUMN(),4)&":"&ADDRESS(ROW()−1,COLUMN(),4), 1)) Where 2 is the first row after headers to be summed

Improve this answer
-2

You can define your expression by writing = sum( and then clicking with your cursor on the first row you want to include and then dragging the cursor down to the last row. This is much simpler than writing the values in.

Below is an image of a sum expression.

enter image description here

The only thing I actually typed was the characters = sum(. After using the cursor to select B2 thru B11 I typed the closing ).

Improve this answer
10
  • For SUM, you really do not need to first write anything, simply select the cell you want the formula in and click Insert > Formula > Sum – user3439894 Sep 13 '19 at 1:30
  • why the downvote? – Natsfan Sep 13 '19 at 2:15
  • no, you write in the cell you want the total to appear. you write "=sum(" without the quotes. Then put the cursor in 1st cell which you want to include and just pull the cursor down to last cell. – Natsfan Sep 13 '19 at 2:18
  • I didn't down vote. – user3439894 Sep 13 '19 at 2:18
  • 2
    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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .