Summing a column in the same column without a circular reference

Question

In Excel you can sum a whole column with =SUM(B:B). But of course you can't use that formula in the column you want to sum, because you'll get a circular reference.

Is there an easy way of summing all the values in the column below the current cell, other than hacks such as =SUM(B2:B65536)?

For reference here's someone asking the same question on ask.metafilter: http://ask.metafilter.com/65955/ExcelFilter-How-do-I-display-the-sum-within-that-column-without-resulting-in-a-circular-reference-error

The suggestion in that thread of =SUM(B:B-B1) is essentially what I'm looking for, if only it actually worked!

Answer 1

In the same thread you pointed as a reference, there is a user proposing a UDF to solve the problem. Search for SUMRANGEWITHEXCEPTION in the text.

With care you may also follow the Excel 2007 help:

If you want to keep the circular reference, you can enable iterative calculations but you must determine how many times the formula should recalculate. When you turn on iterative calculations without changing the values for maximum iterations or maximum change, Office Excel stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first. However, you can control the maximum number of iterations and the amount of acceptable change.

1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
2. In the Calculation options section, select the Enable iterative calculation check box.
3. To set the maximum number of times that Office Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time that Excel needs to calculate a worksheet.
4. To set the maximum amount of change you will accept between calculation results, type the amount in the Maximum Change box. The smaller the number, the more accurate the result and the more time that Excel needs to calculate a worksheet.

Answer 2

This will work in any cell in column B without modification:

=SUM(IF(ROW()=1,0,INDIRECT("$B$1:$B$"&ROW()-1,1)),
      IF(ROW()=65536,0,INDIRECT("$B$"&ROW()+1&":$B$65536")))

Note: This is for Excel versions earlier than 2007.