# How to fix a circular reference in a SUM total

The total includes its own cell. End the range above it: =SUM(B2:B8) in B9, not =SUM(B2:B9).

**Excel:** `=SUM(B2:B8)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Expense | Amount |
| Rent | 1850 |
| Payroll | 12400 |
| Software | 640 |
| Travel | 915 |
| Marketing | 2200 |
| Utilities | 310 |
| Supplies | 185 |
| Total |  |

Formula in B9: `=SUM(B2:B8)`

Result: $18,500

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

## Other ways

- `=SUM(B1:B8)`: 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/circular-reference-in-sum-total