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

June 29, 2007 10:10 PM Subscribe

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

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

2

3 Cheque No.    Federal

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.

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.    Federal

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.

Response by poster: (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

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

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

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

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

Response by poster: @ b1tr0t: sorry mate, doesn't seem work.

posted by chefscotticus at 10:46 PM on June 29, 2007

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

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

posted by yclipse at 5:04 AM on June 30, 2007

**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

Best answer: 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

(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

posted by NailsTheCat at 3:03 PM on June 30, 2007

__contiguous__range. Function 2 works for non-contiguous.posted by NailsTheCat at 3:03 PM on June 30, 2007

« Older i just got a nokia n95 phone. what kinda cool... | Bleeding heart tired of the blood... Newer »

This thread is closed to new comments.

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