My price column will not add up

A column that plainly contains prices sums to zero, or sums to less than it should. Some cells are left-aligned where others are right-aligned.

Why this happens

  • The alignment is the tell. Spreadsheets right-align numbers and left-align text by default, so a left-aligned price is a string that merely looks like a price, and SUM skips it.
  • The usual culprits are a currency symbol typed into the cell, a thousands separator the locale does not recognise, a trailing space, or a non-breaking space where a normal one was expected. All of them are invisible or nearly so.
  • The worst case is not a cell that fails but a cell that reads as something else. “1.240” is one thousand two hundred and forty in most of Europe and one and a quarter almost everywhere else, and both readings are numbers, so nothing anywhere reports a problem.
  • Then there are the cells that are not prices at all. “call for quote”, “TBC”, and “see notes” live in real price columns, and a total that quietly treats them as zero is worse than one that refuses.

What to do right now

  • Sort the column. Text and numbers group separately, which finds the bad cells faster than scanning for them.
  • Use VALUE() or a helper column to convert, rather than reformatting. Formatting changes how a cell looks, not what it is, which is why reformatting appears to fix this and does not.
  • Find the non-breaking spaces specifically. They survive a find-and-replace for a normal space and are the single most common invisible cause.
  • Decide what a non-numeric price should do before you convert anything. Silently becoming zero is a decision, and usually the wrong one.

Stopping it on the next file

A number field coerces rather than merely accepting: currency symbols, percent signs, and every kind of space are stripped, parentheses read as negative, and both the European and Indian grouping conventions are understood, so 1.240,00 and 1,00,000 both arrive as numbers. A cell holding “call for quote” is refused on its own row rather than becoming zero, which is the difference between a total you can defend and a total that is merely present.

The vendor price list template is a sheet with those rules already set, if you want to see the shape of one before building your own.

Other things that go wrong

Merging contact lists gives me duplicatesThree lists went in and the combined file has the same people more than once, often with slightly different spellings, capitalisation, or company names.“N/A” and dashes in a column that should be numbersA numeric column contains “N/A”, “-”, “n.a.”, or “(blank)” alongside real figures. Totals are wrong or the import refuses the whole column.My CSV columns are shifted or split in the wrong placesMost rows look right, but some have values in the wrong columns: a postcode in the country column, a phone number where the email should be. The rows that are wrong all seem to have something in common.My supplier's prices are a thousand times wrongPrices from a European supplier are out by a factor of about a thousand, or occasionally by a factor of a hundred. Nothing errored. The totals are simply wrong, and some individual rows look perfectly reasonable.

Define the sheet once

Name your fields, and every file after that is matched to them and checked cell by cell before a row is allowed to land.

Start freeAll fixes