Hello Colin,

In your small spreadsheet, cursor in cell B18, if I select Sigma function, proposal is to add B13:B17 because it detects intermediate calculus. But selecting B11:B17 get the correct result.

Regards

Jacques Guilleron

Hi there, Jacques,
Sorry, but your analysis is incorrect.
The spreadsheet shows in cell B18 precisely what was included in the purported selection which is not what was selected by the cursor.
The range indicated by the text alongside the column + the empty cell B18 is what was actually selected and then Sigma'd but the process ignored the Sigma function result already in the first selected cell (B11) and artificially selected what is shown in the formula in cell B:18 - B12:B17.
If you now select cells B11:B19 in the spreadsheet you will expect everything in the range to be summed into the empty cell B19 but you will simply still get the result 62 in cell B19 - that is to say, it will now ignore both Sigma'd cells.
Not particularly logical when you're creating a spreadsheet for say a Balance Sheet function where you expect to be able to have a column including sub-totals (B11) and then continue to accumulate to a second sub-total(B18)and potentially further sub totals and then produce a full total much further down the column.

Hello Colin,

Thank you for precisions. My results:
For range B11:B17, Sigma function give 573 in B19. 
For range B11:B18, Sigma function give 635 in B20.
These results on LO 4.0.2.0+, LO 4.0.1.1 and LO 3.6.4.3 on windows 7 Home Premium.
I will download LO 4.0.1.2 to verify on your version.

Regards.

Jacques Guilleron

Hi Again. Jacques,
I'm running Vista (SP2) and all relevant updates. If we continue to achieve different results should I download one of the LO versions you have already verified in your W7 environment and see what happens?
I would only be in a position to attempt this strategy at weekends because my wife uses the current live LO environment for college work on a daily basis so I must leave it stable for her during the week.
Best regards,
Colin

Hi Colin,

The easiest way to do that is to download a LO 4.0.2.x daily build. This is a dev release. It install a second paralell release on your computer and don't touch your installed one.
link : http://dev-builds.libreoffice.org/daily/
But sometimes reinstall the actual version on a new profile may also change its behavior. I have no link for this.

Best regards,

Jacques Guilleron

Created attachment 76604 [details]
updated spreadsheet

Hi there, Jacques,
Sorry, power failure right in the middle of sending the update so the covering message somehow got "lost in the post".
Vista Home Basic 32 (SP2) with seven user accounts and UAC
3 Kids with parental controls
2 Adults no controls no admin privileges
2 Admin accounts - one for software testing.
Downloaded LODevVersion 4.0.3.0+
(Build ID: d4b4a6a88bbab383cef4bf4076a84e19efa509a)to the test account so I'm assuming that the Dev version in a different user account with admin privileges is equivalent to a whole new environment.
Still exhibiting the same error type but I actually extended the test, as can be seen from the replacement spreadsheet which did "make the cut" on the last message.
I actually made a short .AVI video with CAMSTUDIO to demonstrate the point but it's too big to upload. If there's an upload location for bigger files (c57Mb)that I can use, then I can distribute it that way.
If I am going to get involved in further testing (again, subject to the spouse priority user caveat), I will probably sort out a personal online store somewhere but that's not going to happen fast enough for this issue!
Best regards,
Colin

Hi there again, Jacques,
Sorry if this is becoming verbose, I have now had the opportunity to perform a more systematic test. It is questionable as to whether it is a genuine bug or a simple but fairly significant inconsistency in the manner in which cells can be identified for the SUM and Sigma functions. There appear to be three methods.
Firstly, delete the contents of cells B11, B18 and B21 from the sample spreadsheet.
1. Cursor in B11 > Sigma -- result = selection marquee for SUM of B2:B10
(the selection marquee may be re-positioned and re-sized according to requirements) > CR to accept the formula and parameters -- result = SUM of B2:B10 automatically placed in B11  which is the first available empty cell.
alternatively (clear B11)
2. Cursor in cell B1 > left mouse and hold, selecting range B2:B10(orB2:B11) > Sigma -- result = SUM of B2:B10 automatically placed in B11 which is the first available empty cell.
alternatively (clear B11)
3. Cursor in cell B11 > type =SUM( > manually select cells B2:B10 > CR to accept the formula and parameters -- result = SUM of B2:B10 automatically placed in B11 which is the defined target location
All reasonably logical
Now try
4. Cursor in B18 > Sigma -- result = selection marquee for SUM of B13:B17
(the selection marquee may be re-positioned and re-sized according to requirements) > CR to accept the formula and parameters -- result = SUM of B13:B17 automatically placed in B18  which is the first available empty cell.
alternatively (clear B18)
5. As 4 above but re-position and re-size the selection marquee to include cells B11:B17 -- result = cumulative total of B11:B17 in cell B18.
alternatively (clear B18)
6. Cursor in cell B11 > left mouse and hold, selecting range B11:B17(orB11:B18) > Sigma -- result = SUM of B13:B17 automatically placed in B18 which is the first available empty cell but NOT The Required (SUM(B11:B17)) Calculation. The formula can be edited to provide the desired result.
Now try
7. Cursor in B21 > Sigma -- result = selection marquee for SUM of B18:B18
(the selection marquee may be re-positioned and re-sized according to requirements) > CR to accept the formula and parameters -- result = SUM of B18:B18 automatically placed in B21  which is the target cell.
alternatively (clear B21 but leave existing formula in B11)
8.Cursor in cell B11 > left mouse and hold, selecting range B11:B21 > Sigma -- result = SUM of B19:B20 automatically placed in B21 which is the first available empty cell but, ironically, the SUM is 0 all "real data" in the range B13:B17 and the preexisting sub totals in B11 & B18 have been completely ignored. Definitely NOT The Required Calculation. The formula can be edited to provide the desired result.
It could be argued that it is illogical to include the real numbers and their sub total in a later total but that should flag an error condition rather than just ignoring all data elements and providing the false result of 0.

For the record
clear B21
Cursor in B21 > type =sum( > select the range B10:B18 > CR -- result = mathematically correct but a totally illogical use of the data.

We cannot program user data abuse out of the product but we should not leave illogical operation of valid requests in it either

As demonstrated, the results of a manual selection of a range for Sigma are not only handled incorrect but also inconsistently, depending on just how many Sigma'd cells are included in the selection.
I have not attempted to test for a greater number of data blocks with sub totals but could undertake that fairly easily if it becomes a requirement.
It could be that further input from the original analyst may be necessary to ascertain just what they think the procedures should be, or, we can cheat a little and see what happens in Excel :).
Best regards,
Colin

Hi Colin,

Here, It's quite clear and reproductible. I set status to NEW.

Have a nice day,

Jacques Guilleron

I have tested this rather awful bug under Ubuntu 10.04 x86_64 and Crunchbang 11 x86_64 running the following versions of LO:

- v3.3.0.4 OOO330m19 (Build:6)
- v3.4.6.2 OOO340m1 (Build:602)
- v3.5.7.2 Build ID: 3215f89-f603614-ab984f2-7348103-1225a5b
- v3.6.7.2 Build ID: e183d5b
- v4.0.5.2 Build ID: 5464147a081647a250913f19c0715bca595af2f
- v4.1.3.2 Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a

All exhibit the same behaviour, which is to say that the method of highlighting cells and clicking the AutoSum (Σ) toolbar button results in an =SUM() function range only including any immediately prior cells not containing another =SUM() function. For example: 

1. Enter 1 in A1.
2. Enter 1 in A2.
3. Highlight A1:A3 and click Σ; =SUM(A1:A2) is entered in A3.
4. Enter 1 in A4.
5. Enter 1 in A5.
6. Highlight A1:A6 and click Σ; =SUM(A4:A5) is entered in A6.

Setting Version to v3.3.0 release, Platform to All/All.

>[...] this rather awful bug [...]

I would like to clarify my prior comment in the context of what is stated in comment #1, that the AutoSum (Σ) toolbar button "detects intermediate calculus". Evidently the AutoSum function is designed to automatically locate and select prior =SUM() totals i.e., selection / highlighting of the range to sum prior to clicking this button is not required. For example: 

1. Enter 1 in A1.
2. Enter 1 in A2.
3. Place cursor on A3.
4. Click Σ; =SUM(A1:A2) is selected / entered in A3.
5. Enter 1 in A4.
6. Enter 1 in A5.
7. Place cursor on A6.
8. Click Σ; =SUM(A4:A5) is selected / entered in A6.
9. Place cursor on A7.
10. Click Σ; =SUM(A3;A6) is selected / entered in A7.

At the very least this would appear to offer a viable workaround.

I am seeing this same problem with LO Mac 4.3.1.2 and whatever is current in Manjaro Linux.

Still there in 4.4.6.3 Win 10

This bug is still present in LibreOffice 5.1.4.2 (July 2016) on any OS.
With the same behavior reported in 2013.

still present in Version 5.3.0.1 (x64) 

Januar 2017

This is on purpose. An AutoSum stops at a previous SUM function and does not include it. This is because users enter values, click on AutoSum, enter other values, click on AutoSum again. This should not include the previous SUM. If you want a sum of sums then, after the last sum, click AutoSum and it will sum only the SUM cells.