SUM filtered rows in LibreOffice using SUBTOTAL

In Libre Office Calc you sometimes need to filter data using the Autofilter. And of course you would like to know the sum of the values filtered. This is where you'll want to use the SUBTOTAL() function. To get the subtotal of the filtered values you'll need to tell the subtotal function which function you would like to use on the filtered rows.

The source

Suppose that you have a multiple of rows of sales for the last week. Column A contains the date of the sale and column B contains the total for one particular sale. Column C contains the name of the sales person.

Here's what this table looks like:

Filtering

Now you would like to see the sales for december 23rd 2014.
You select cell A1 and go to: Data -> Filter -> AutoFilter
The column headers will now show as dropdown list boxes where you can select the values you want. In this particular example we'll select december 23rd.

Here's the table after selecting december 23rd:

As we can see the calculations in cells B24 and B25 have changed, and showing only the sum of the filtered cells. (In column C I have shown the functions used in col B) With this simple function we can filter out the values we want filtered and get simple readable data back.

There is also a subtotal menu choice that can be used: Data -> Subtotals to produce subtotals for you. Try it to see which you prefer.

Function Index

For those who have read this far; this is a list of the first parameter in the SUBTOTAL() function and the meaning of it:

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

As we can see the SUBTOTAL() function can be used for quite complex calculations. This article is only meant as a starting point for the SUBTOTAL() function.