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

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

• 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. :(