Why your Excel or Google Sheets total is wrong: 7 real causes, ranked

A wrong total in a spreadsheet is rarely a mystery. It is almost always one of seven things, and six of them announce themselves within a minute if you ask the right question. The trouble is that the usual first move - staring at the formula - is the one that tells you least. The formula is usually fine. It is the data underneath that is lying to it.

So start with the data.

The 60-second triage

Put these next to your data - say your numbers live in B2:B500:

Two more worth knowing: =SUMPRODUCT(--ISTEXT(B2:B500)) counts text cells directly, and in Excel, tapping F9 is a one-second test for a stale total (see the note at the end). Now the seven, in the order they actually turn up.

1. Your numbers are text

This is first by a wide margin, and it earns the place because SUM's behaviour is documented and merciless: text in a referenced range is ignored, not flagged. No error, no warning - just a total that is missing the rows it silently declined to add. The same silence has a bigger version outside the spreadsheet: a product importer that reports success and quietly skips the rows it declined.

Where the text comes from, roughly in order: a CSV or platform export where every column arrives as text; a leading apostrophe someone typed to "keep the formatting"; a currency symbol or thousands separator typed into a text-formatted cell; a cell formatted as Text before the number was entered; and the nastiest one - a non-breaking space (character 160) riding along from an HTML or web export. That last one is invisible, and TRIM will not remove it: TRIM was built for the plain ASCII space, character 32. You need =VALUE(SUBSTITUTE(TRIM(A2),CHAR(160),"")).

In Google Sheets the same fixes apply, minus the green triangle. Sheets sees more of this than Excel does, for one boring reason: locale. A European export written 1.234,56 dropped into a US-locale sheet isn't a number at all - it's a string with punctuation in the wrong places. Re-import with the right locale, or convert explicitly.

2. The range stopped growing

Cause #1 is the loudest; this one is the most expensive, because it never looks like a bug. Revenue is "down a bit this quarter" and nobody suspects the total - they suspect the market. Inserting a row inside a range extends it automatically, which is exactly why people trust ranges they shouldn't: the failure only shows up when data is appended past the end, which is how every export in the world adds data.

Excel Tables are the real fix rather than the clever one. A Table knows where its data ends, so the total, the chart and every formula referring to it move together. Whole-column sums work too, as long as the column holds nothing but that data - no stray note at the bottom, no second total parked underneath.

3. Rows are counted twice

Compare =COUNTA(A2:A500) with =COUNTUNIQUE(A2:A500) in Sheets, or =SUMPRODUCT((A2:A500<>"")/COUNTIF(A2:A500,A2:A500&"")) in Excel. A gap means repeated keys - which is either duplicate rows, or a legitimately repeating key that your lookup is about to multiply.

4. The lookup matched the wrong row

This one is dangerous precisely because it is not an error. =VLOOKUP(A2,Prices!A:C,3) - no fourth argument - will happily return a price. Just not always the right one. On sorted data it mostly works, which is how it survives review; on unsorted data it returns whatever happened to sit above the gap.

Two companions worth checking at the same time. Key mismatch: "00123" as text and 123 as a number are different keys, and so are "Acme Ltd" and "Acme Ltd " with its trailing space - the second is a genuine, and genuinely infuriating, cause of a report being 40% #N/A. Column drift: a VLOOKUP's column index is a plain number, so the day someone inserts a column into the source table, 3 quietly starts pointing at the wrong column. XLOOKUP and INDEX+MATCH refer to the column itself and survive it.

5. Hidden and filtered rows are still in the total

The real damage here isn't the wrong number, it's the screenshot: a filtered view and a SUM in the same picture, pasted into an email, read by someone who reasonably assumes they belong to each other. If a total is going to be read next to a filter, it has to obey the filter. That is SUBTOTAL's entire job, or AGGREGATE's if you also want it to step over error values while it works.

6. IFERROR turned your errors into zeros

Of the seven, this is the one I'd call self-inflicted, and it comes from a good instinct. The sheet was full of #N/A, someone tidied it up, and the tidying worked: the errors are gone. So is the information that 60 rows never found a price. A zero is a claim - it says "this row contributed nothing" - and IFERROR let the sheet make that claim 60 times without being asked.

If that IFERROR was written for you by an AI, it is worth knowing why: asking a model to make the errors stop is exactly the request that produces a silencer. I take that apart in ChatGPT wrote your script. Your real data broke it.

The rule I hold to on every file I touch: a guard formula may flag a problem, never swallow it. "CHECK" in a cell is impossible to ignore and impossible to sum. A zero is easy to ignore and sums beautifully. If a number is going to be wrong, let it be loudly wrong.

7. Rounding: what you see isn't what you sum

A hundred rows each hiding a third decimal will drift the total by a few cents, and "a few cents" is enough to make a finance team distrust the whole report - correctly, as it happens, because a number that can't be reproduced by hand isn't much of a number.

Underneath this sits real floating-point arithmetic: Excel follows the IEEE 754 standard and keeps 15 digits of precision, so a handful of decimal fractions genuinely cannot be stored exactly, and the classic 0.1 + 0.2 lands a hair off 0.3. It's rarely your actual bug - display rounding beats it to the punch nearly every time - but it's the reason the fix is ROUND at the point of calculation rather than trusting the arithmetic to be tidy.

The one that isn't a cause at all

Before you go hunting: in Excel, press F9. If the total changes, nothing was broken - the workbook was in manual calculation mode and the total was simply stale. Someone switched it on years ago to speed up a heavy file, and it followed the file forever. Google Sheets has no equivalent, which is one thing it genuinely gets right.

Which one is it: symptom table

Six changes that stop it coming back

The reconciliation cell is the one to take if you take only one. Everything above is about finding a wrong number; that cell is about the wrong number finding you, immediately, which is a much better arrangement. It's also the cheapest thing on the list - a subtraction and a conditional format.

Common questions

My SUM shows 0, but the cells clearly contain numbers.

They contain things that look like numbers. SUM ignores text in a referenced range rather than erroring, so a column of text numbers totals to a confident 0. Run =COUNT(B2:B500) against =COUNTA(B2:B500): COUNT sees only real numbers, so if it comes back lower, that's your answer in about four seconds.

Why doesn't formatting the column as Number fix it?

Because a number format governs how a value is displayed, not what the value is. Text wearing a Number format is still text. You have to convert: Convert to Number on Excel's error indicator, a Paste Special multiply by 1, or VALUE(). In Sheets, VALUE() or a re-import with the correct locale.

The total changed after someone sorted the sheet.

Usually the sort covered part of the table rather than all of it - one column selected instead of the whole range - so rows were scrambled against each other. The arithmetic may be flawless while every row now pairs the wrong client with the wrong amount. Undo immediately if you can, and sort the whole table, or use an Excel Table, which sorts as a single object.

Is Google Sheets more reliable than Excel for this?

No. All seven exist in both, with the same fixes. Sheets sees more text-number trouble, because it lives on imports and locale mismatches. Excel sees more stale totals, because it has a manual calculation mode. Pick on other grounds.

How long does it take to find which one it is?

The triage is three formulas and about a minute, and in most files it names the cause outright. The fix is usually the same day once you know what you're fixing. The honest exception: a report assembled from several exports whose totals disagree because the sources disagree. No formula shortens that one - it's reconciliation work, and anyone promising otherwise hasn't opened the file.