4

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!

2 Answers 2

2

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.
2
  • Thanks for that. Unfortunately neither is what I'd class as "easy" :\ Aug 24, 2010 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, 2010 at 8:31
1

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.

2
  • you are assuming a 65536 maximum, which is not the case in newer versions of Excel. Sep 22, 2013 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. Sep 22, 2013 at 17:38

You must log in to answer this question.

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