Adding all the values below the current cell in Excel

Question

I am trying to display the total sum of all the numbers for a particular column. I want the sum to be displayed above the column as follows:

21   30
A    B
6    5
6    10
6    10
3    5

I know I can sum the values and display it at the bottom of the column using =SUM(A3:INDIRECT("D"&ROW()-2)), however I am not getting a way to display it at the top of the column.

Please guide.

Answer 1

When I'm doing something like this, I prefer to not include any empty cells beneath the range I'm summing, because I've had errors in the past as the result of including them (usually because there's a cell way down in the column somewhere that I'm not expecting to have a value). I'm assuming that A & B are your column headers. Assuming that, here is how I would do it. This is your formula for cell A1:

=SUM(OFFSET(A$1,2,0,COUNTA(A$3:A$65535)))

Explanation

I'm updating this with a brief explanation, per the OP's request.

According to ExcelFunctions.net:

The Excel Offset function returns range of cells that is a specified number of rows and columns from an initial supplied range.

The function reference for OFFSET is:

=OFFSET(reference, rows, cols, [height], [width])

What this formula does is create a dynamic range based on the number of cells in the selection, relative to cell A$1. This is an offset of two rows and no columns, which starts the range at A$3. The height of the range is the total number of filled cells in the range A$3:A$65535. The assumption here is that there are no blank cells in the range, which there were not in the sample data.

Answer 2

Based on the comments and the previous answers I suggest following formula, entered in cell A1:

=SUM(OFFSET(A$2,0,0,ROWS(b:b)-1))

You can then copy/paste to the right till second last column.

You could also modify your formula in A1 like this to achieve the same:

=SUM(INDIRECT("A2:A"&ROWS(A:A)-2))

But then you cannot copy/paste to the right...

A more general approach with your idea would be:

=SUM(INDIRECT(ADDRESS(ROW()+1,COLUMN())&":"&ADDRESS(ROWS(A:A),COLUMN())))

You can then copy/paste to the right till last column.

Some explanations:

• Both formula sums up every value in the range from A2 till the bottom of column A (i.e. for Excel 2010 this would be A2:A1048576)
• It doesn't matter if there are blanks or cells without value; the formula sums up only the numbers
• I put A$2 and B:B in the OFFSET formula to avoid circular references, since I'm writing in cell A1 and I cannot write A$1 nor A:A
• With the INDIRECT formula you don't have to worry about circular references

Further commenting (sorry, I don't have the credits to comment at the right place under the question):

• Phylogenesis formula =SUM(A3:A65535) could also do the work, isn't it?
• Didn't understand your question at first, because you talk of "sum of all the numbers for a particular row" but then you sum columns, isn't it?