4
1
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!
Thanks for that. Unfortunately neither is what I'd class as "easy" :\ – therefromhere – 2010-08-24T14:09:31.230
@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 – 2010-08-25T08:31:55.320