Ask Your Question

Does SUBTOTAL have Functions which ignore hidden data? [closed]

asked 2014-02-07 22:58:41 +0200

co_buckfast gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 07:01:53.575330

1 Answer

Sort by » oldest newest most voted

answered 2014-02-08 01:34:47 +0200

m.a.riosv gravatar image

About the SUBTOTAL() function in the inner help, I think is what you are looking for.

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.
SUBTOTAL(Function; Range)
Function is a number that stands for one of the following functions:
Function index
2     COUNT
3     COUNTA
4     MAX
5     MIN
7     STDEV
8     STDEVP
9     SUM
10    VAR
11    VARP

Range is the range whose cells are included.
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:
edit flag offensive delete link more


This is awesome! Thanks.

frozenjim gravatar imagefrozenjim ( 2019-02-02 16:20:19 +0200 )edit

Question Tools

1 follower


Asked: 2014-02-07 22:58:41 +0200

Seen: 5,629 times

Last updated: Feb 08 '14