Fix the 0 error

What 0 means

When SUM returns 0 or an incorrect total, the most common cause is that the cells being summed contain numbers stored as text — they look like numbers but the SUM function treats them as zero. Other causes include a circular reference causing a 0 result, or calculation being set to manual so the formula is not updating.

Common causes

• Numbers are stored as text — imported from CSV, copied from a web page, or prefixed with an apostrophe. SUM treats text as 0.
• The spreadsheet is in manual calculation mode (Excel: Formulas > Calculation Options > Manual) so SUM is showing a stale result and not recalculating.
• A circular reference: the SUM range includes its own cell (e.g. =SUM(A1:A10) in cell A1), causing the result to be 0 or to iterate incorrectly.
• The range is filtered and SUM adds all rows including hidden rows — this is usually correct behaviour but confusing if the user expects only visible rows.
• Mixed content in the range: some cells contain text labels, error values, or empty strings that disrupt the expected total (though SUM ignores text, #VALUE! from errors in the range propagates).

Example fix

When A2:A100 contains a mix of real numbers and text-numbers, SUM returns 0 for text entries. SUMPRODUCT with VALUE forces conversion so all numeric-looking values are summed. Fix the source data for a long-term solution.

How to fix it

1. 1Diagnose text-numbers: select a blank cell, type 1, copy it, select the SUM range, Paste Special > Multiply (Excel) or Paste Special > Values only then multiply with a formula. Alternatively use =SUMPRODUCT(VALUE(A2:A100)) to force conversion.
2. 2Switch calculation back to automatic: in Excel go to Formulas > Calculation Options > Automatic. In Google Sheets, calculations are always automatic — if the result is stale, press Ctrl+Shift+F9 to force a full recalculation.
3. 3Find and fix circular references: Excel shows 'Circular Reference Warning' in the status bar. Go to Formulas > Error Checking > Circular References to locate the cell. Remove the cell from its own SUM range.
4. 4To sum only visible (filtered) rows, use SUBTOTAL: =SUBTOTAL(9,A2:A100) sums only the visible cells. SUBTOTAL(109,...) also ignores manually hidden rows.
5. 5If cells in the range contain errors, find and fix the error source. You can also use =SUMIF(A2:A100,"<>*",A2:A100) to sum only numeric cells.

Frequently asked