Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In cell A1 I can't write =SUM(A:A) (Circular reference). If I write =SUM(A2:A15) when I insert A16 the result won't be affected. How to do this?

share|improve this question
up vote 3 down vote accepted

To sum up all cells in the entire column except the one cell in the same column that you put the formula in (namely A1), use


for MS Office 2007 and later, or use


for MS Office 2003 and earlier.

share|improve this answer
Will that work for MEAN too? Will it consider the blank lines? – Jader Dias Jul 29 '11 at 13:29
I think you mean AVERAGE() function. Yes, it works for it as well. It does not evaluate the empty cells. – Mehper C. Palavuzlar Jul 29 '11 at 13:39
Yes, Average. Mean is the Octave name for the same method. – Jader Dias Jul 29 '11 at 13:53

You could move the =SUM(A:A) to another column or you could set A1 as =SUM(A2:A20000).

share|improve this answer
+1. Note that it is considered bad form to have the sum of an entire column in that same column, so the first solution (move the sum to another column) would be your best bet. – Breakthrough Jul 29 '11 at 13:33

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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