ExcelFilter: How do I display the sum within that column without resulting in a "circular reference" error?

June 29, 2007 10:10 PM

ExcelFilter: How do I display the sum within that column without resulting in a "circular reference" error?

I'm building a spreadsheet to keep track of my paychecks. I'd like to keep track of YTD totals of a column (eg, federal taxes) at the top of that column, and find a formula that I can enter once into the top that will calculate the sum for the rest of the column for as many entries as I choose to make. Simply summing the entire column results in a circular reference error.

In other wods, for the simplified example below, give me a function for [SUM_FEDERAL] (in B1) that will sum the individual values for each cheque without me having to adjust/copy the function for each entry of a new cheque. (I know I could simply do =SUM(B4:B100), and adjust after 100 entries, but I want a solution that both scales and makes it simple to maintain).


AB
1YTD [SUM_FEDERAL]
2
3 Cheque No. &nbsp &nbspFederal
4015
5028
6037


I imagine that there is a super simple solution -- after several hours of playing around and searching Google, I've given up and turned to MeFi.

posted by chefscotticus to computers & internet (13 comments total)

If I am picturing this correctly... and maybe I am not...

Why not make a sum cell way at the bottom of the column (A500 or whatever) then make A1 (=A500)

I must be missing something.
posted by The Deej at 10:17 PM on June 29, 2007

(Hmm. Tables don't work in MeFi apparently. See the formatted example here: http://img39.picoodle.com/img/img39/8/6/30/f_askmefiexcem_0896809.jpg)
posted by chefscotticus at 10:21 PM on June 29, 2007

You're concerned about outgrowing whatever limit you set, right? In Excel 2000, you cannot increase the number of worksheet rows beyond 65,536. So you'll never outpace SUM (B2:B65536).
posted by ormondsacker at 10:23 PM on June 29, 2007

I don't use Excel, but I think a better formulation of this question is that you want a function that does:

Sum the cells in a column starting from a fixed address n to the last non-blank cell in that column.

Although you are probably over-thinking the whole thing and I recommend ormondsacker's fix.
posted by demiurge at 10:26 PM on June 29, 2007

For column B:

=sum(b:b-b1)

posted by b1tr0t at 10:28 PM on June 29, 2007

(put that formula in cell b1, obviously)
posted by b1tr0t at 10:28 PM on June 29, 2007

@ b1tr0t: sorry mate, doesn't seem work.
posted by chefscotticus at 10:46 PM on June 29, 2007

Did you mean to best-answer yourself?

I don't have Excel loaded on this computer to test, but if you're looking for more elegance, I think this should work as a generalized solution:

=SUMIF(A:A, <>$A$1, B:B)

Basically, that looks at each row in turn to see if the A value is the same as the cell in A1 - if it's different, it adds that B value to the overall total. The way your spreadsheet is set up, that should add all B values except the one in row 1, preventing a circular reference. I think Excel is smart enough to pull that off, but check it first.
posted by ormondsacker at 11:33 PM on June 29, 2007

If you insert a new line among the lines being summed, then the sum will automatically be adjusted to include the new entry as well as all the previous entries. This works best when the sum is at the end, forcing you to insert new lines to add more data.
posted by alexei at 11:35 PM on June 29, 2007

By the way, you should get in the habit of putting the sum cell at the top of the column of numbers being added, rather than at the bottom. The sum is very often the bit of information that the user first wants to see, and it should display as soon as she navigates to that sheet.
posted by yclipse at 5:04 AM on June 30, 2007

I think you're being a little too much of a purist here. It is possible however -- you need to use a UDF

(User Defined Function). Here's a couple of ways of doing it.

(1) Only works for single column, non contiguous range -- but it's fast.

Place the following formula in your cell B1:

=SUMRANGEWITHEXCEPTION1(B:B,B1:B4)

And insert a macro module into your workbook and place the following code in there:

Public Function SUMRANGEWITHEXCEPTION1(rng1 As Range, rng2 As Range) As Double

Dim rngToSum As Range
Dim lCountExclusions As Long
Dim lRow As Long

' Must be single column and non-contiguous range

' get a new range that excludes rng2 from rng 1
' we'll use lcount to tell when we've finished the exclusion
' and can therefore stop -- to speed it up
With rng1
For lRow = 1 To .Rows.Count
If lCountExclusions = rng2.Cells.Count Then Exit For
If Intersect(.Cells(lRow, 1), rng2) Is Nothing Then
If rngToSum Is Nothing Then
Set rngToSum = Range(.Cells(lRow, 1))
Else
Set rngToSum = Union(rngToSum, .Cells(lRow, 1))
End If
Else
lCountExclusions = lCountExclusions + 1
End If
Next lRow

'On Error Resume Next
If rngToSum Is Nothing Then
Set rngToSum = Range(.Cells(lRow, 1), .Cells(.Rows.Count, 1))
Else
Set rngToSum = Union(rngToSum, Range(.Cells(lRow, 1), .Cells(.Rows.Count, 1)))
End If
On Error GoTo 0

End With

SUMRANGEWITHEXCEPTION1 = WorksheetFunction.Sum(rngToSum)

Set rngToSum = Nothing

End Function

(b) A more universal solution (but slower):

use :
=SUMRANGEWITHEXCEPTION2(B:B,B1:B4)

and the following code:

Public Function SUMRANGEWITHEXCEPTION2(rng1 As Range, rng2 As Range) As Double
Dim c As Range
Dim rngToSum As Range
Dim rngNonBlanks

' This supports multiple columns and non-contiguous ranges

' get a new range that excludes rng2 from rng 1
For Each c In rng1.Cells

If Intersect(c, rng2) Is Nothing Then
If rngToSum Is Nothing Then
Set rngToSum = c
Else
Set rngToSum = Union(rngToSum, c)
End If
End If
Next c

SUMRANGEWITHEXCEPTION2 = WorksheetFunction.Sum(rngToSum)
Set c = Nothing
Set rngToSum = Nothing

End Function

All they do is sum column B as the normal sum function does. But they exclude from the sum the range passed

as the second argument thereby sidestepping the circular range issue. I think I have a neater solution -- but I have to go dig up weeds. :(
posted by NailsTheCat at 1:54 PM on June 30, 2007

Errr... I meant of course that function 1 only works for single column contiguous range. Function 2 works for non-contiguous.
posted by NailsTheCat at 3:03 PM on June 30, 2007

=SUM(B4:B65536)

I do this, and it works.
posted by NortonDC at 2:54 PM on July 1, 2007