Calc gives out incorrect SUM

[hunk]

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.

[Kruno]

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.

[mikekaganski]

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.

[hunk]

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 5.3.1.2

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.

[mariosv]

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.

[hunk]

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.

[mikekaganski]

Just unmerge your D4.

[hunk]

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

[mikekaganski]

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.

[mikekaganski]

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.

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

[AdmFubar]

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