grand total subtotals without hiding details

[Sherry]

Hello,
I have a worksheet with just under 700 lines. This worksheet consists of
employees, their department and their payroll life insurance deductions for a
specific pay date. I have grouped them by department and subtotaled the
deductions. Is there a formula that tells excel I want a grand total that
only adds the subtotals (because all values are in one column...I suppose I
could sum and divide by 2?) Actually that works!

Well, I'll leave the post here in case anyone else is trying to get a
solution!

Thanks for giving me a forum to talk out my problem!

Sherry

 

[Peo Sjoblom]

There is a subtotal formula function as well

=SUBTOTAL(9,Range)

 

[Sherry]

Hi Peo,

What goes in the brackets?

There is a subtotal formula function as well

=SUBTOTAL(9,Range)

 

[Peo Sjoblom]

For example

D2200

if that is the range you want to sum

 

[Rick Rothstein \(MVP - VB\)]

The Range should be obvious, so I assume your question deals with the "9".
That first argument is a function number. There are 11 different function
numbers that can be specified there (actually, 22, 11 that include hidden
values and 11 that exclude them). The function number "9" makes the SUBTOTAL
the cells in the Range whereas the other function numbers do other things.
You should check out the Help files for SUBTOTAL to see everything it can
do.

Rick

 

[Sherry]

Hi Peo,

I tried the following formula =subtotal(9,h2:h690) and I still had to divid
by 2. If you can see the error I'm making, would you please point it out.
Thanks again!
Sherry (all values are in column H; column H also contains the subtotals by
department)

 

[Dave Peterson]

Did you insert rows that contained subtotals in the middle of your data?

If you did, then those formulas could have been
=subtotal(9,h2:h5)
=subtotal(9,h7:h19)
....
instead of:
=sum(h2:h5)
=sum(h7:h19)

=subtotal() will ignore other =subtotal() formulas.

 

[Sherry]

I think I'm getting it now
I've entered =sum(range), so when I use =subtotal(9,range) it's not ignoring
the 'sum' formulas but it does ignore the 'subtotal' formulas and give me the
right answer when I use =subtotal(9,range) for all of the departments.
Thanks for all your help guys!