Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.
share|improve this answer
    
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.

share|improve this answer
    
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

Your Answer

 
discard

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.