Ask Your Question

Calc: SUM() function getting wrong results

asked 2016-12-30 14:38:14 +0100

nclm gravatar image


I have a spreadsheet document that is quite important, with numbers and sums. The number in each cell is entered as a manual sum of other numbers (=11+22+33, for instance), and the sums are bold numbers that should regroup some of the original numbers with the SUM() function.

Here's the number in J24:
Here's the sum, obviously wrong, of C13:C24:

The other bold number, wrong too, is meant to be a sum of C4:C12.

What do you think is wrong? How can that happen?

Thank you a lot for your help,
~ Nicolas

edit retag flag offensive close merge delete


1876 is the correct result to the formula =756+210+910?
The result in J24 is the result obtained for C24 first?
Default alignment is left for text and right for numbers.
Thus the suspicion by @floris v that the other results in the range C12:C24 might be of type text.
Formulae can well produce results looking like numbers but actually being texts. In addition 'Numbers' formats can lie about the true value... Nobody can find out the truth about this by looking at a screenshot.

Lupp gravatar imageLupp ( 2016-12-30 20:46:31 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2016-12-30 15:39:41 +0100

floris v gravatar image

updated 2016-12-30 16:49:09 +0100

The most likely explanation is that Calc doesn't recognize all of the numbers as numbers but as strings. Select Tools - Options - Calc - View, then tick Value highlighting. Numbers will then be displayed in blue, text in black, formulas as green. Format any wrong cells as Number, and remove any apostrophes at the start of numbers to convert them to real numbers instead of text.


If this answer helped you, please vote it with ✔ (here on the left). That will help other people with the same question.

edit flag offensive delete link more


Hi, thanks for your answer. When I turn the highlighting, they all turn green! And they stay the same color once I format them as numbers. No blue, no black.

nclm gravatar imagenclm ( 2016-12-30 16:43:51 +0100 )edit

Can you upload a sample file instead of a screenshot?

floris v gravatar imagefloris v ( 2016-12-30 17:28:28 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-12-30 14:38:14 +0100

Seen: 950 times

Last updated: Dec 30 '16