Subtotal 9 and Grouped Cells

rocksolid77

New Member
Joined
Sep 19, 2011
Messages
18
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... not sure why groups and filters get along so poorly) 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:
Code:
=SUBTOTAL(9,A3:A3000)
"Global" Rows:
Code:
=IF(SUBTOTAL(9,A3:A5)=0,"",SUBTOTAL(9,A3:A5))
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.
 
Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi,

Filter and group would likely act against each other. Regardless of your filter, resetting your group would give the subtotal of your whole range even if only a few rows should have been selected by the filter.
 
Upvote 0
Thanks for the reply. I think it may be a different issue since I'm seeing a subtotal of 0, not my full range. I'm aware that expanding the groups will in effect nullify the filtering. The issue I'm seeing is happening while the groups are collapsed, so the filtering is working in the sense that only the groups for "Platform 1" are showing once collapsed but my subtotal is 0.
 
Upvote 0
Small addendum.

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... Only once a filter has been activated though, if there's no filters at all on the sheet then the subtotals show the numbers within the collapsed group just fine.
 
Upvote 0
sorry kinda difficult to duplicate your situation, however I do understand what you mean, but have no solution to suggest.
 
Upvote 0

Forum statistics

Threads
1,212,098
Messages
6,105,953
Members
447,986
Latest member
dicklim39

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top