Ask Your Question

Calc gives out incorrect SUM

asked 2017-09-12 12:40:04 +0200

hunk gravatar image

updated 2017-09-12 12:40:32 +0200

I have a spreadsheet that i use to keep track of ticket sales in 3 cinemas.

Since the movies are screened twice a day, for two or three days, i have individual cells that contain the ticket sales for every screening, and then add all those up for the total ticket sales for each movie. Then I sum up all those movie sums to get the total amount of tickets issued. That's where all hell breaks loose. In the previous iteration of the file, I would get up to 2000 more tickets than those issued. I deleted the column and re-did the formulas, and it seemed to work OK for a few days, but it's now starting to give out wrong sums again.

If I select all the individual sales, the sum given at the status bar is correct, and looking at each movie's total there doesn't appear to be any errors. But then the SUM of those is somehow bigger.

I've made sure to check that the values in the cells are indeed numbers (where applicable, since there are text notes in there for a few days), by activating the "Value Highlighting" option.

I can't post a photo, so I hope I'm describing it good enough.

edit retag flag offensive close merge delete


Hold down CTRL and select only cells containing sums for each screening and then see what status bar gives. Make sure you don't have any hidden rows or columns as sum function will add that to calculation too.

Kruno gravatar imageKruno ( 2017-09-12 12:57:06 +0200 )edit

Without a test file, there's nothing to say. You could upload it somewhere, and post a link here. And give at least some info: OS, LO version.

Or at least provide an exact descriptions (values and formulas) that give the wrong result, which result do you get and which do you expect, so that others could try to test.

Mike Kaganski gravatar imageMike Kaganski ( 2017-09-12 13:05:16 +0200 )edit

I've already done that, both by holding down shift and selecting the range, and by individually clicking each cell, and I get mixed results. For instance now that I tried it again, I not only get a wrong sum, but twice the value of the erroneous sum! Last night, I would get the correct sum showing.

In regards to specs, I'm using Win 10, and my LO is version

Not sure, I could upload the file itself, due to the nature of it, but I'll try to create a truncated one and upload it.

hunk gravatar imagehunk ( 2017-09-12 13:10:44 +0200 )edit

Maybe you are sorting with Menu/Tools/Options/LibreOffice Calc/General - Update references when sorting range of cells. What can modified the sum ranges in the formulas.

m.a.riosv gravatar imagem.a.riosv ( 2017-09-12 15:27:36 +0200 )edit

I've uploaded a truncated version of the file here

It should be enough to showcase what I'm saying, without exposing potentially confidential information.

Besides the wrong sum, I should also point out that I had a few additional columns with costs, and gain/loss calculated. Those columns had conditional formatting to turn them red or green depending on gain/loss, and that seems to have stopped working since posting.

hunk gravatar imagehunk ( 2017-09-13 09:25:16 +0200 )edit

Just unmerge your D4.

Mike Kaganski gravatar imageMike Kaganski ( 2017-09-13 09:44:40 +0200 )edit

Damn, that was embarrassing. Is there any way to check for hidden values in merged cells?

hunk gravatar imagehunk ( 2017-09-13 09:59:39 +0200 )edit

Well, you can select the cell and look into the status bar's "count" and "sum" values.

But when I say "select", I mean the kind of selection you make when drag many cells (so that the area is highlit), not the kind you get when click a cell (so it stays white, just gets a thick border). To select a single cell this way, you start dragging from the cell, move the mouse to a neighbour cell, then return it back and then release the mouse. Ctrl+Click is not enough. Note the range name box.

Mike Kaganski gravatar imageMike Kaganski ( 2017-09-13 10:08:12 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-09-13 10:27:58 +0200

Mike Kaganski gravatar image

updated 2017-09-13 10:30:43 +0200

To sum up the discussion in the comments to the question into a useful answer:

The SUM (and other functions) takes into account all cells that constitute function's range. You should pay attention to cases where the value is hidden/invisible:

  1. Hidden rows/columns (those with size 0)
  2. Cells with formatting making value invisible (using font color, or number formatting like here)
  3. Merged cells.

The latter is the cause in the case of this question. When one merges cells with existing values, LO asks user if it needs to move everything into first cell, keep all cells values, or remove all but first.

image description

In second case, the cells continue to have (now hidden) values, so they will take part in calculations over that range.

edit flag offensive delete link more

answered 2017-09-14 00:21:28 +0200

AdmFubar gravatar image

In the june 29, and 30th Total Sales cell. Split the cell. There are two distinct =sum() formulas there.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-12 12:40:04 +0200

Seen: 50 times

Last updated: Sep 14