Subtotal 9 and Grouped Cells

Hi Guys,

Basically I have a spread sheet that lists projects and hours worked by region with a totals row at the top... so:

..............Totals.....=Subtotal(9,...)

Project 1 - Region 1 - Platform - #
Project 1 - Region 2 - Platform - #
Project 1 - Region 3 - Platform - #
Project 1 - Global - Platform - =Subtotal(9,A3:A5)

The rows are grouped so that only "Global" is showing when collapsed. Global # is the sum (Subtotal,9) of the three cells above it.

The totals row is set up as Subtotal,9 so it avoids all the "Global" rows which will remove any duplicate values.

So far so good, Subtotals works exactly as I'd like it to, giving the same value both when all rows are expanded and all rows are collapsed. Here's where it gets messed up.

If I filter out a value in "Platform" (excel expands all groups) and then collapse the remaining cells all my subtotals go to 0. I'm not sure why since Regions 1-2-3 are not being filtered, just hidden via the group. My understanding was that Subtotal 9, excluded only cells which are auto filtered while subtotal,109 excluded auto filtered and hidden cells. Here are the functions I'm using:

Totals Row:

Please Login or Register  to view this content.

"Global" Rows:

Please Login or Register  to view this content.

If I collapse the groups individually the subtotal still reverts to 0, if I expand a group individually it shows the correct subtotal again. It's really strange, I get the impression it's functioning as a Subtotal,109 instead of Subtotal,9 and is disregarding hidden cells or that collapsed groups are being counted as filtered... this only occurs once a filter has been activated, if there's no filters at all on the sheet then the subtotals show the numbers within the collapsed group just fine.

Can anyone let me know why this is happening and any possible solutions? Do grouped cells count as filtered once a filter has been initiated? Any help will be much appreciated, thanks in advance.

I've attached a sample spreadsheet.Excel Help sample Sheet.xlsx

Hi Martin,

Thanks a lot for your time. You've hit the nail on the head; my issue is that there is a multitude of information on my sheet which goes to superiors who are of course, always trying to filter by one criteria or another. I was hoping there was some workaround I can use so that they can keep the streamlined collapsed view once they're filtered. It's not a big deal but I'd prefer they don't need to go hunting around for the "Global" row of the project they'd like to look in on among the hundreds of other rows.

Any ideas would be greatly appreciated.

Thanks Oeldere, that's a good idea! I'm actually really disappointed in myself for not thinking of it. Thanks to both of you gentlemen for your time.

Did you ever find a solution to this without having to use a pivot table? I'm having exactly the same problem where I need to filter a column but then collapse subtotals to level 2 to show a subtotal only. All the subtotals are showing zero when I do this.

Hey Trevor

Pivot tables are really the best way to go. If you're not familiar with them yet I'd recommend you take some time to learn. Knowing pivot tables has done wonders for me at work. I promise it's not nearly as intimidating as it seems once you learn the basics

Thanks guys, I have used a pivot table but in this instance my boss wants to use the filter and subtotal method as the file will be updated weekly by someone who can't use pivot tables. I thought the filter and subtotal would be quick and easy method for them to use.

Thanks Oeldere, I have started a new thread today.