FormulaCraft

Fix the 0 error

Excel & Google Sheets
Topic:SUM & aggregation

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

Example fix

Broken
=SUM(A2:A100)
Fixed
=SUMPRODUCT(VALUE(IF(ISNUMBER(A2:A100),A2:A100,0)))

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.

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

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.

Stop hunting errors by hand.

Upload your spreadsheet and the Auditor flags every 0 and broken formula at once — or paste this one formula and get the fix explained.

Frequently asked

How can I tell if my numbers are stored as text?

Numbers stored as text typically left-align in the cell (real numbers right-align by default). In Excel a small green triangle appears in the top-left corner. Use =ISNUMBER(A2) — TRUE means a real number, FALSE means text or blank.

SUM was working fine and suddenly returned 0 — I did not change anything.

The most likely cause is that calculation mode was accidentally switched to Manual (Ctrl+Alt+F9 triggers recalculation in Excel, or Formulas > Calculation Options). A circular reference introduced by a recent edit is the second most likely cause.

How do I sum only visible rows after applying a filter?

Use SUBTOTAL(9, range) which automatically skips rows hidden by a filter. For rows hidden manually (not by filter), use SUBTOTAL(109, range).

More on SUM & aggregation

See all →
Reference

SUMPRODUCT

Multiply ranges together row by row and sum the result — ideal for weighted totals.

How-to

How to sum a column

Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.

Reference

SUBTOTAL

Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.

How-to

How to create a running total

Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.

How-to

How to sum values by month

Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.

How-to

How to sum the top N values

Add up the largest few numbers in a range using LARGE. Here we total the top 3 — the same in both apps.

Related formulas

SUMSUMIFSUMIFSVALUESUBTOTALSUMPRODUCT

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: