Subtotal function acts weird

[sulliwane]

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 !

[mariosv]

Because SUBTOTAL() works taking only the visible cells.

You can use SUMIF() to get what you want.
=SUMIF($B$1:$B$8;“brigitte”;$A$1)

[sulliwane]

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

[LibreNut]

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.
Scenario:
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.