We will be migrating from Ask to Discourse on the first week of August, read the details here

 Ask Your Question

# Subtotal function acts weird [closed]

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 C:\fakepath\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 !

edit retag reopen merge delete

## 2 Answers

Sort by » oldest newest most voted

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)

more

## Comments

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

( 2012-10-13 07:40:58 +0200 )edit

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.

more

## Stats

Asked: 2012-10-12 23:44:25 +0200

Seen: 1,235 times

Last updated: Feb 24 '14