My price column will not add up
A column that plainly contains prices sums to zero, or sums to less than it should.
Why it happens
- The alignment is the tell.
- 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.
- The worst case is not a cell that fails but a cell that reads as something else.
- Then there are the cells that are not prices at all.
What to do now
- Sort the column.
- Use VALUE() or a helper column to convert, rather than reformatting.
- Find the non-breaking spaces specifically.
- Decide what a non-numeric price should do before you convert anything.
Next time
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.
Start from the vendor price list template.
Related problems
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.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.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.