Description: I used to use formulas like =SUBTOTAL(9;G5:G99999) and =SUM(G5:G99999) to count all amounts in column, with and without filters, respectively. Using row number 99999 might look ambiguous, but it's just "somewhere far far away, definitely below the end", and it was working well--until now. After update to 25.8 release, LibreOffice Calc always automatically changes my =SUBTOTAL(9;G5:G99999) formulas to something like =SUBTOTAL(9;G5:G6512) where 6512 is the last row number on the sheet. Thus, after adding next row, I have to change the range by hand. That is super annoying, broke existing sheets and is not error-proof. Steps to Reproduce: 1. Start with empty sheet 2. Put some numbers to cells A2, A3, A4, A5 (e.g., 10, 20, 30, 40) 3. Put formula =SUBTOTAL(9;A1:A99999) to cell A1 Actual Results: The formula in A1 is changed to =SUBTOTAL(9;A2:A5) Expected Results: The formula in A1 should be =SUBTOTAL(9;A2:A99999) Reproducible: Always User Profile Reset: No Additional Info: Fortunately, =SUM(A2:A99999) behavior is not affected
Can reproduce. Setting to "NEW". Version: 26.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: fd48da7236f26d9d8d16759546a5c860f91b82de CPU threads: 12; OS: Linux 6.14; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: CL threaded
*** Bug 168539 has been marked as a duplicate of this bug. ***
*** Bug 168218 has been marked as a duplicate of this bug. ***
*** Bug 169826 has been marked as a duplicate of this bug. ***
bibisected with linux-64-25.8 commit b89047a0f0100fb30121084cf42815aa792c1f88 author Balazs Varga tdf#164843 - sc optimize "SubTotal" function's reference ranges Double references from relative named ranges can point to large ranges (MAXCOL/MAXROW) and because of that some function evaluation like SubTotal can be extreamly slow when we call ScTable::CompileHybridFormula with these large ranges. Since all the SubTotal functions ignore empty cells its worth to optimize and trim the double references in SubTotal functions. *** adding CC: Balazs Varga Please, take a look?
Struggling with this bug too (v. 26.2.3.2) There is also one more side effect, probably from the same cause: if I create a named range "TestRange", set it to "A1:A999" and then add new data below existing rows to column A - the value of "=SUBTOTAL(9;TestRange)" is not updated. This is even worse than entering "A1:A999" in SUBTOTAL function directly, as there are no visible changes and nothing signals the user that something has gone wrong. Currently the only workaround I've found is using "=AGGREGATE(9;5;A1:A999)" instead of "=SUM(9;A1:A999)", which thankfully works.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8647fa47438a3531c1387d75023bbb6db7bdbc24 tdf#168013 - sc: don't mutate SubTotal reference when trimming It will be available in 26.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Works well. Version: 26.8.0.0.alpha1+ (X86_64) Build ID: 24dd7cf9ebb47a05e4bc864d6bf7e5db1f39ccfc CPU threads: 4; OS: Windows 11 X86_64 (build 26200); UI render: Skia/Raster; VCL: win Locale: ja-JP (ja_JP); UI: ja-JP Calc: CL threaded