grand total subtotals without hiding details

S

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
 
R

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
 
S

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)
 
D

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

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top