grand total subtotals without hiding details
 

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

grand total subtotals without hiding details
 

There is a subtotal formula function as well

=SUBTOTAL(9,Range)


--
Regards,

Peo Sjoblom



"Sherry" wrote in message
...
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

grand total subtotals without hiding details
 

Hi Peo,

What goes in the brackets?

"Peo Sjoblom" wrote:

There is a subtotal formula function as well

=SUBTOTAL(9,Range)


--
Regards,

Peo Sjoblom



"Sherry" wrote in message
...
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

grand total subtotals without hiding details
 

For example

D2:D200

if that is the range you want to sum


--
Regards,

Peo Sjoblom



"Sherry" wrote in message
...
Hi Peo,

What goes in the brackets?

"Peo Sjoblom" wrote:

There is a subtotal formula function as well

=SUBTOTAL(9,Range)


--
Regards,

Peo Sjoblom



"Sherry" wrote in message
...
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

grand total subtotals without hiding details
 

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" wrote in message
...
Hi Peo,

What goes in the brackets?

"Peo Sjoblom" wrote:

There is a subtotal formula function as well

=SUBTOTAL(9,Range)


--
Regards,

Peo Sjoblom



"Sherry" wrote in message
...
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

grand total subtotals without hiding details
 

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)

"Peo Sjoblom" wrote:

For example

D2:D200

if that is the range you want to sum


--
Regards,

Peo Sjoblom



"Sherry" wrote in message
...
Hi Peo,

What goes in the brackets?

"Peo Sjoblom" wrote:

There is a subtotal formula function as well

=SUBTOTAL(9,Range)


--
Regards,

Peo Sjoblom



"Sherry" wrote in message
...
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

grand total subtotals without hiding details
 

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 wrote:

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)

"Peo Sjoblom" wrote:

For example

D2:D200

if that is the range you want to sum


--
Regards,

Peo Sjoblom



"Sherry" wrote in message
...
Hi Peo,

What goes in the brackets?

"Peo Sjoblom" wrote:

There is a subtotal formula function as well

=SUBTOTAL(9,Range)


--
Regards,

Peo Sjoblom



"Sherry" wrote in message
...
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







--

Dave Peterson

grand total subtotals without hiding details
 

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!
"Dave Peterson" wrote:

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 wrote:

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)

"Peo Sjoblom" wrote:

For example

D2:D200

if that is the range you want to sum


--
Regards,

Peo Sjoblom



"Sherry" wrote in message
...
Hi Peo,

What goes in the brackets?

"Peo Sjoblom" wrote:

There is a subtotal formula function as well

=SUBTOTAL(9,Range)


--
Regards,

Peo Sjoblom



"Sherry" wrote in message
...
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







--

Dave Peterson