Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 02 Feb 2019 16:20:19 +0100Does SUBTOTAL have Functions which ignore hidden data?https://ask.libreoffice.org/en/question/29554/does-subtotal-have-functions-which-ignore-hidden-data/MS Excel has a series of SUBTOTAL Functions which ignore hidden data. These Functions are numbered similarly to the base Functions, but starting at 100. For example,
Use all data and get the AVERAGE: 1
Ignore hidden data and get the AVERAGE: 101
It does not appear that LibreOffice support these 100 series Functions.
Is that correct?
Is there another way to get the same behavior?
Is this a feature planned for future implementation?Fri, 07 Feb 2014 22:58:41 +0100https://ask.libreoffice.org/en/question/29554/does-subtotal-have-functions-which-ignore-hidden-data/Answer by m.a.riosv for <p>MS Excel has a series of SUBTOTAL Functions which ignore hidden data. These Functions are numbered similarly to the base Functions, but starting at 100. For example,
Use all data and get the AVERAGE: 1
Ignore hidden data and get the AVERAGE: 101</p>
<p>It does not appear that LibreOffice support these 100 series Functions.</p>
<p>Is that correct?</p>
<p>Is there another way to get the same behavior?</p>
<p>Is this a feature planned for future implementation?</p>
https://ask.libreoffice.org/en/question/29554/does-subtotal-have-functions-which-ignore-hidden-data/?answer=29558#post-id-29558About the SUBTOTAL() function in the inner help, I think is what you are looking for.
SUBTOTAL
Calculates subtotals. If a range already contains subtotals, these are not used for further calculations. Use this function with the AutoFilters to take only the filtered records into account.
Syntax
SUBTOTAL(Function; Range)
Function is a number that stands for one of the following functions:
Function index
Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
Range is the range whose cells are included.
Example
You have a table in the cell range A1:B5 containing cities in column A and accompanying figures in column B. You have used an AutoFilter so that you only see rows containing the city Hamburg. You want to see the sum of the figures that are displayed; that is, just the subtotal for the filtered rows. In this case the correct formula would be:
=SUBTOTAL(9;B2:B5)
Sat, 08 Feb 2014 01:34:47 +0100https://ask.libreoffice.org/en/question/29554/does-subtotal-have-functions-which-ignore-hidden-data/?answer=29558#post-id-29558Comment by frozenjim for <p>About the SUBTOTAL() function in the inner help, I think is what you are looking for.</p>
<pre><code>SUBTOTAL
Calculates subtotals. If a range already contains subtotals, these are not used for further calculations. Use this function with the AutoFilters to take only the filtered records into account.
Syntax
SUBTOTAL(Function; Range)
Function is a number that stands for one of the following functions:
Function index
Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
Range is the range whose cells are included.
Example
You have a table in the cell range A1:B5 containing cities in column A and accompanying figures in column B. You have used an AutoFilter so that you only see rows containing the city Hamburg. You want to see the sum of the figures that are displayed; that is, just the subtotal for the filtered rows. In this case the correct formula would be:
=SUBTOTAL(9;B2:B5)
</code></pre>
https://ask.libreoffice.org/en/question/29554/does-subtotal-have-functions-which-ignore-hidden-data/?comment=181566#post-id-181566This is awesome! Thanks.Sat, 02 Feb 2019 16:20:19 +0100https://ask.libreoffice.org/en/question/29554/does-subtotal-have-functions-which-ignore-hidden-data/?comment=181566#post-id-181566