Mac Forum / Applications / Excel / March 2006

This really piqued my curiousity, since I've never seen subtotals in
all of my years.  They're even a menu option!  (I've always been a
sumif kind of guy.) Being curious, I created a sheet that used them.
What I found was that if you create a SUBTOTAL over a range containing
SUBTOTALs, it picks up only the SUBTOTALs.  Thus, you don't need to
reference the specific cells containing the subtotals, just the range
of which they're a part.

If the tabs are preserved in this post, copy and paste the following
starting at A2:
region    salesman    sales q1    sales q2    sales q3    sales q4
east    dizzy    57    5    31    15
east    dizzy    53    14    25    61
        =SUBTOTAL(9,C3:C4)    =SUBTOTAL(9,D3:D4)    =SUBTOTAL(9,E3:E4)    =SUBTOTAL(9,F3:F4)
east    hero    39    82    49    53
east    hero    31    41    32    48
east    hero    42    59    46    97
        =SUBTOTAL(9,C6:C8)    =SUBTOTAL(9,D6:D8)    =SUBTOTAL(9,E6:E8)    =SUBTOTAL(9,F6:F8)
north    hugh    9    93    44    15
north    hugh    47    8    44    76
north    hugh    76    65    74    97
        =SUBTOTAL(9,C10:C12)    =SUBTOTAL(9,D10:D12)    =SUBTOTAL(9,E10:E12)    =SUBTOTAL(9,F10:F12)
north    papa    33    67    14    78
north    papa    60    83    44    96
        =SUBTOTAL(9,C14:C15)    =SUBTOTAL(9,D14:D15)    =SUBTOTAL(9,E14:E15)    =SUBTOTAL(9,F14:F15)
south    cornelius    13    67    73    87
        =SUBTOTAL(9,C17:C17)    =SUBTOTAL(9,D17:D17)    =SUBTOTAL(9,E17:E17)    =SUBTOTAL(9,F17:F17)
south    tramp    57    96    98    41
south    tramp    55    57    66    41
        =SUBTOTAL(9,C19:C20)    =SUBTOTAL(9,D19:D20)    =SUBTOTAL(9,E19:E20)    =SUBTOTAL(9,F19:F20)

summary
q1    =SUBTOTAL(9,C3:C21)
q2    =SUBTOTAL(9,D3:D21)
q3    =SUBTOTAL(9,E3:E21)               
q4    =SUBTOTAL(9,F3:F21)