How to fix a circular reference in a SUM total
Formula (Excel and Google Sheets)
=SUM(B2:B8)The total includes its own cell. End the range above it: =SUM(B2:B8) in B9, not =SUM(B2:B9).
- Result on the example
- $18,500 in
B9 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in B9. Change any input and the result updates, or edit the formula to experiment.
How it works
A total that includes its own cell, like =SUM(B2:B9) in B9, depends on itself: a circular reference. Excel warns and shows 0. Ending the range on the last expense row, B8, removes the loop.
=SUM(B2:B8)Result18500
B2:B87 rows × 1 col: $1,850, $12,400, $640, $915, …A range: 7 cells in one column.
SUM(B2:B8)18500SUM: Adds numbers and ranges. Skips text and blanks.- number1
B2:B87 rows × 1 col: $1,850, $12,400, $640, $915, …
- number1
In Google Sheets
The same formula works in Google Sheets: =SUM(B2:B8). We ran it in Google Sheets mode on the same data and it returns $18,500.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(B1:B8) | $18,500 | Starting at the header row is harmless, since SUM skips the text "Amount", and rows inserted right under the header are then included. |
Common mistakes
=SUM(B:B)placed in column B is circular too. Put whole-column totals in another column.- Turning on iterative calculation silences the warning but doesn't make the total right.
- The status bar names the cell with the circular reference; Formulas, Error Checking, Circular References lists them all.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Fix a circular reference in a SUM total
B9, total the expenses above it without including the total cell itself.Questions
Why does my total show 0?
Excel stops calculating a circular reference and shows 0 or the last value it had.
How do I keep the total right when I add rows?
Insert rows inside the range, above row 8, and the range grows. In Excel, =SUM(B2:INDEX(B:B,ROW()-1)) always stops just above the total, wherever rows are added.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.