Subtotal function acts weird

Hi all,

I have two columns : first one for incomes et second for people. I activated the auto-filter thus I can display all incomes for a particular person. (see attached subtotal-example.ods).

My question : I tried to use the subtotal function to track the total amount of incomes for each person…but as soon as my display filter parameters change, the subtotal change :o(

Do I use the subtotal function the wrong way or simply misunderstood it ? and above all, how would you achieve this goal (total based on display filter results)

Thank you !

Because SUBTOTAL() works taking only the visible cells.

You can use SUMIF() to get what you want.

thanks a lot ! that was exactly what I was looking for…nice to get the right awser so quicky :slight_smile:

I was facing a similar problem, and I found the solution here.
Novices like me have difficulty understanding and following these steps, so I’m writing the steps again.
You have a list of rows with subtotals. You want only the subtotal rows (results) to be displayed so that you can work on the subtotals - maybe arrange the subtotals in ascending order or whatever. Follow these steps:

  1. Display all the data with the subtotals
  2. Select all the cells and using Date - Define Range give it a name. Named ranges store subtotals, filters etc
  3. Using Date - Filter - Standard Filter set criteria - Field name - Name of the column by which subtotals are grouped; Condition - “contains”; Value - “Sum”
  4. Click on + sign Options and check “Copy results to” and select a cell in a different sheet
  5. Now only your subtotal rows are available for further work

Use of Data Pilot (Pivot Table) is supposed to be an alternative but I have not worked on that.