# Summing a column in the same column without a circular reference

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)`?

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

-

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.
-
Thanks for that. Unfortunately neither is what I'd class as "easy" :\ – therefromhere Aug 24 '10 at 14:09
@therefromhere - Unfortunately, I think these are as easy as it gets. Excel does not provide this functionality, so you either have to build it in yourself (with a UDF), work around it (by allowing circular ref's with a single calculation step) or just build formulas that address the range as per `=SUM(B2:B65536)`. – DMA57361 Aug 25 '10 at 8:31

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.

-
you are assuming a 65536 maximum, which is not the case in newer versions of Excel. – törzsmókus Sep 22 '13 at 17:30
@törzsmókus, thanks, edited in note. I don't know the new number off the top of my head, so feel free to add it to the note if you do. – Lance Roberts Sep 22 '13 at 17:38