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]

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

sulliwane gravatar image

updated 2012-10-12 23:48:13 +0200

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 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 11:51:11.303513

2 Answers

Sort by » oldest newest most voted

answered 2012-10-13 02:02:09 +0200

m.a.riosv gravatar image

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)

edit flag offensive delete link more


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

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

answered 2014-02-24 14:16:57 +0200

Libre Nut gravatar image

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.

edit flag offensive delete link more

Question Tools


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

Seen: 1,235 times

Last updated: Feb 24 '14