Subtotal 9 and Grouped Cells

[rocksolid77]

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:

=SUBTOTAL(9,A3:A3000)

"Global" Rows:

=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.

 

[cyrilbrd]

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.

 

[rocksolid77]

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.

 

[rocksolid77]

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.

 

[cyrilbrd]

sorry kinda difficult to duplicate your situation, however I do understand what you mean, but have no solution to suggest.