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?

2 Answers 2


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.

  • Will that work for MEAN too? Will it consider the blank lines?
    – Jader Dias
    Commented Jul 29, 2011 at 13:29
  • 1
    I think you mean AVERAGE() function. Yes, it works for it as well. It does not evaluate the empty cells. Commented Jul 29, 2011 at 13:39
  • Yes, Average. Mean is the Octave name for the same method.
    – Jader Dias
    Commented Jul 29, 2011 at 13:53

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

  • +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. Commented Jul 29, 2011 at 13:33

You must log in to answer this question.

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