Share via

SUMOFFSET & Circular Reference Error

Anonymous
2014-01-07T15:03:47+00:00

Hello,

I’ve starting using SUMOFFSET as follows to capture whole ranges as I'm getting tired of always updating sum ranges whenever I add a row to the bottom of a calculation.

=SUM($M$10:OFFSET($M$19,-1,0))

Within the function I use the cell for the total is as the base of the formula, but this seems to create a circular reference error in Excel even though it’s perfectly fine.  The supervisor that reviews my work isn’t very sophisticated with Excel & keeps getting confused about the circular reference.  Is there a way to write the formula to not have the error?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-02-17T19:56:56+00:00

    Has anyone been able to solve this?  I use this formula all the time as well and I keep getting Circular Reference Errors.  Any help is much appreciated.

    0 comments
  2. Anonymous
    2014-01-07T15:50:44+00:00

    What is in cell M19? Are there other values in column M below M19?

    You could try the old accounting trick of summing the entire range and dividing by two to account for including subtotals in the range.

    0 comments
  3. Anonymous
    2014-01-07T15:42:32+00:00

    The supervisor doesn't want to make there data points tables which is why I've moved to SUMOFFSET.

    0 comments
  4. Anonymous
    2014-01-07T15:30:10+00:00

    Convert your ranges to tables, and the formulas will automatically update, with formulas that will look like:

    =SUM(Table1[Header])

    instead of

    =SUM(M10:M19)

    0 comments