Hi
I found that the sum function summarize rows that are hidden !
Is there maybe option somewhere where you can disable it ?
Thanks
Obviously, this question is a Calc one. So, retag to replace common by calc.
Hello,
use SUBTOTAL(109,A1:A10) (assuming you want add values in range A1:A10). If you hide(!) any row within the range the result will adapt. For more information about 109 (and other values <=> operation modes <=> function indices of function SUBTOTAL()) please refer to:
LibreOffice Help - Mathematical Functions - SUBTOAL
Hope that helps.
I would like to add that using SUBTOTAL() for the purpose may come with risks concerning the correct interpretation of results, and calculations based on these results in subsequent steps.
In addition the function excludes results in the respective ranges already calculated by SUBTOTAL().
Do you feel sure to be able to correctly regard this all in every case? I don’t, and I therefore I never use the function.
As far as filtering is concerned: It must be based on data, and the best way to do it, is to have a dedicted row column for the filtering condition. If you have such a column, you can easily get formulas explicitly using the same conditions for selecting the values to work with.
By the way: Will you always know which rows are filtered out, and which ones are arbitrarily hidden? SUBTOTAL() wit first parameter <100 will treat them differently!
Ok thanks that worked , not sure why you would have a SUM() function but calculate hidden rows. Wonder if this is bug !
Wonder if this is bug !
No, intended behavior and that’s why there is a function SUBTOTAL() with function index 109.
@Pieter.za: Like @Opaque already told, its not a bug. To be able to calculate sums (and many different results) independent of hiding and filtering is essential for the functionality of spreadsheets.
You can get results restricted to the evaluation of rows under explicit conditions using the functions like SUMIFS(), AVERAGEIFS(), and many more.
The additional implementation of a means like SUBTOTAL() is, IMO, a concession to the generation WYSIWYG, and as many means of the kind it comes with serious risks (as already pointed out).
Be explicit! Dedicate a column to each condition you want to evaluate, fill it by directly entering your “checkmarks” or with formulas calculating conditional results. This way you can conditionally SUM() or do whatever you want based on the conditions, this including filtering.