41

I want to have a column in Excel that consists of a header, a bunch of numbers, and then have the sum of those numbers at the bottom. I'd like the sum to adapt to the insertion of new numbers above the total. Something like this:

Numbers
 1
 2
 5
10

18        Total

If I later insert 10 new numbers in the middle of the list, I want the sum to automatically include them.

I know the SUM() function can sum a whole column, but if the total is also in that column then it complains about a circular reference. How can I just sum the numbers above the total?

74

The functions ROW() and COLUMN() will give the current cell's row and column. Use them in the ADDRESS() function to create a string representing the range from the top of the current column to the row above the total. Then use the INDIRECT() function to turn that string into a real range to give to the SUM() function. The formula for the total cell would then be:

=SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))

Put that in any cell in a spreadsheet and it will produce the sum of all the numbers in the column above that cell.

9
  • 1
    @user370646 Actually, I just tested it with Excel 2010 using several different types of formulas in different cells being added and it worked correctly. (It does fail with a circular reference error if the cells being added contain a formula that references the cell with the sum.) Do you have specific examples? – Mark Meuer Sep 23 '14 at 21:01
  • 2
    +1 times a million. This works in Google Sheets where the ranges of sum functions -DON'T- automatically get updated. Thank you so much. – seekerOfKnowledge Oct 27 '14 at 14:22
  • 2
    I just tested in Google Sheets and the combination of functions work perfectly. – Sun Jun 4 '16 at 3:53
  • 3
    This answer saved my life! – kRazzy R Aug 4 '18 at 6:25
  • 1
    Seems to work in LibreOffice Calc too. – Dejan Jan 20 '19 at 11:49
9

This answer is specific to Libra Office but should work for Excel as well.

Basically, if we want to explicitly mention the row/column to be frozen, we should mention the $ with that row/column.

*There is an inbuilt algo as well such that when you copy the formula to other cells, Libra Office will auto-determine how it should change the formula values.

So, lets assume you have to add all rows G1:GN => where N is the current row number. For this you want to fix the row of first value and keep other values as. So the formula will be :

=SUM(G$1:G1) -> for 1st row and then you can copy-paste the formula to other cells.

Libra Office will automatically keep G1 as first value and dynamically change 2nd value to G2, G3 and so on for every cell.

1
  • As per Apostolos55's answer > =Sum($A1:A1) < , it would not work as only the column A will be fixed while Row will still vary when formula is copied to other cells in the same column. So, the $ should be appended before the Row to get the correct values in the formula. Further, it did not explain what can be other combinations, so I provided the alternate. The $ has to be applied on both Row and Column if the cell has to be kept constant. – Prateek Jan 25 '16 at 11:41
8

Actually, you can do what you want with plain =SUM()

Assuming your Excel sheet has the following design:

\|  A  |  B  |
-+-----+-----+
1|  1  |     |
2|  2  |     |
3|  3  |     |
4|  4  |     |
5|  5  |     |
6|     |     |
7|  15 |Total|

Assumng A7 is =SUM(A1:A5), you can add more rows as you please, as Excel will expand the =SUM's range accordingly.

Assume now that I select row 2, 3 and 4, and I insert rows. The result will be the following:

 \|  A  |  B  |
--+-----+-----+
 1|  1  |     |
 2|     |     |
 3|     |     |
 4|     |     |
 5|  2  |     |
 6|  3  |     |
 7|  4  |     |
 8|  5  |     |
 9|     |     |
10|  15 |Total|

A10 equals =SUM(A1:A8). As such, you can now insert new numbers at will.

4
  • 1
    Now that you spell it out, I knew of this behavior. But for some reason I was thinking it would be handy to just have a formula that would always sum from just above the current cell. But it is obviously easier to create the simple sum range than the complex formula I came up with. I was too smart by half. Thanks! (I'll have to console myself that I at least learned about INDIRECT and ADDRESS, which I hadn't used before.) – Mark Meuer Oct 18 '13 at 22:42
  • 3
    I have changed the accepted answer to the one I posted as that one keeps getting upvotes over a year later, does the job I asked in the question, and apparently works in Google spreadsheets where this answer does not. But, @Doktoro, your answer provided very useful insight and I am grateful for it. – Mark Meuer Jan 22 '15 at 21:35
  • 1
    There is a flaw: Insert a row at the end (before the empty row). At least in LibreOffice, this will leave the SUM-formula with its original range, i.e. omitting the new row. My solution: SUM(OFFSET(A$1; 0; 0; ROW(A7)-ROW(A$1); 1)). Because it refers to itself and not a row above, it will always adjust the range correctly. – Tilman Vogel Dec 3 '15 at 18:15
  • The @TilmanVogel 's shoud be an answer! Thank you so much – Genarito Aug 12 '19 at 12:38
3

ok, Indirect() is volatile... so as your sheet grows bigger, it will only get slower. If it suites you, you'd better use:

=Sum($A1:A1)

when you drag this formula down, you will always have a range from A1 till the current (or previous etc) cell. No Volatile, very fast, much much more simple!

Editing after 7years to add one small piece for completeness of unorthodox and not 100% answers! Use cyclic reference! So you add C1 to C5 at C6, then simply write at c6 = sum(c$1:c6)-c6 press enter choose OK (at the warning of cyclic reference) go to file / options / formulas / calculation options check [iterative calculations] set [maximum iterations] = 1 now you are done!

++ if follows you no matter what... ++ you can use cyclic formulas! :D:D ---- if you accidentally write cyclic formula you will not be notified :((

3
  • Thank you for the answer. Please help me understand, how is your answer different from that of @Doktoro Reichard? – Mark Meuer Mar 7 '15 at 22:57
  • This is sum that can "follow" alongside your cells, say in next column. Then with an IF you can only show/calculate last Sum. Probably not what you are looking for but good to know. – Apostolos55 Mar 9 '15 at 8:51
  • Thanks for the information. You're right that it does not solve the problem from the original question (which is specifically looking for a formula to sum the number above the cell) but may be helpful in other situations. – Mark Meuer Mar 9 '15 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.