58

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?

Improve this question
1

4 Answers 4

Reset to default
91

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.

Improve this answer
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, 2014 at 21:01
  • 3
    +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, 2014 at 14:22
  • 2
    I just tested in Google Sheets and the combination of functions work perfectly.
    – Sun
    Jun 4, 2016 at 3:53
  • 3
    This answer saved my life!
    – kRazzy R
    Aug 4, 2018 at 6:25
  • 1
    Seems to work in LibreOffice Calc too.
    – Dejan
    Jan 20, 2019 at 11:49
19

This answer is specific to Libre 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 a built-in algorithm as well such that when you copy the formula to other cells, Libre 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.

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

Improve this answer
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, 2016 at 11:41
9

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.

Improve this answer
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, 2013 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, 2015 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, 2015 at 18:15
  • The @TilmanVogel 's shoud be an answer! Thank you so much
    – Genarito
    Aug 12, 2019 at 12:38
7

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 :((

Improve this answer
3
  • Thank you for the answer. Please help me understand, how is your answer different from that of @Doktoro Reichard?
    – Mark Meuer
    Mar 7, 2015 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, 2015 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, 2015 at 18:56

You must log in to answer this question.

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