Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Duplicate Running Total Grand Total In Pivot Table

Good Morning All, XL2007
I have a basic PT summing values across a row of months

A B C D
Overheads 10 10 10
Expenses 20 20 20
Grand Total 30 30 30

Now I wish to insert another Grand Total Row which will show
the Running Balance ie

A B C D
Overheads 10 10 10
Expenses 20 20 20
Grand Total 30 30 30
Grand Total(2) 30 60 90

I know I could add a seperate row outside the PT to reflect the
Running Total, but would rather keep it included, via a
Calculated Field or such like.
I have managed to contrive a Calculated field, but this returns Subtotal
rows
for the Running Balance for all my Subdivisions, eg Overheads, Expenses etc.
I wish just the Grand Total(2) to show the Running Balance.

Is there a way?

As usual thank you for any input.
Cheers
Mathew


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Duplicate Running Total Grand Total In Pivot Table

Use sumproduct. Put this formula in column B the copy across all your columns

=sumproduct(--($A1:$A1000="Grand Total"),B1:B1000)

Row 1000 is the row before the final grand Total. Therefore this formula
will go in the next row B1001.

"Mathew P Bennett" wrote:

Good Morning All, XL2007
I have a basic PT summing values across a row of months

A B C D
Overheads 10 10 10
Expenses 20 20 20
Grand Total 30 30 30

Now I wish to insert another Grand Total Row which will show
the Running Balance ie

A B C D
Overheads 10 10 10
Expenses 20 20 20
Grand Total 30 30 30
Grand Total(2) 30 60 90

I know I could add a seperate row outside the PT to reflect the
Running Total, but would rather keep it included, via a
Calculated Field or such like.
I have managed to contrive a Calculated field, but this returns Subtotal
rows
for the Running Balance for all my Subdivisions, eg Overheads, Expenses etc.
I wish just the Grand Total(2) to show the Running Balance.

Is there a way?

As usual thank you for any input.
Cheers
Mathew



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Tabe Duplicate Grand Total Mathew P Bennett Excel Discussion (Misc queries) 0 August 16th 08 09:59 AM
Pivot Table Grand Total Tufail Excel Discussion (Misc queries) 2 January 9th 08 01:23 PM
Pivot Table (Grand Total) IF Statement[_2_] Excel Discussion (Misc queries) 1 November 19th 07 08:07 PM
Pivot Table Grand Total Stonewall Excel Discussion (Misc queries) 3 June 12th 07 12:25 AM
Grand total in Pivot-Table TaiwanSwede Excel Worksheet Functions 0 January 10th 06 04:03 AM


All times are GMT +1. The time now is 07:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"