Sub-totals outside of Pivot Table

[sogc]

Has anyone every had to create a column adjacent to a pivottable and then tried to create sub-totals and grand-total for that manual column?

I'm having a hell of a time trying to figure this one out either with an Excel worksheet formula or via VBA.

I had to create a new column adjacent to my pivot table, because the values of the new column were based on the values of the pivot table (whether totals from inside the pivot table are positive or negative). Now I want sub-totals from my new column.

I cannot put the values of my new column inside the source data as the values of the new column are a formula based on the pivot table data.

Here's an example.

[tt][b]
Day    Time    Dollars    Positive Values[/b]
Sun    9AM     $25        $25
Sun    10AM    $(10)      $0[b]
TOTAL SUN      $15        $25[/b]

Mon    9AM     $(15)      $0
Mon    10AM    $45        $45[b]
TOTAL Mon      $30        $45

GRAND TOTAL    $45        $70[/b]
[/tt]

I cannot figure out how to total the 25, 45 and 70 in the Positive Values column. This must be either a formula that I can fill down or I need to populate it via VBA.

Any ideas?

 

[sogc]

I made an error in my pivot data above. There is only one instance of Sun and one instance of Mon per normal pivot table grouping function. Therefore, I cannot base a SUMIF formula on that column. This is the trouble I'm having with just using a Worksheet formula.

 

[SkipVought]

Hi,

Why not put another column in your source data for pos values? Then include in PT.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[sogc]

Skip,

I cannot do that. Because I need to calculate based on the sum of the values and whether the sum is positive or negative. Not the individual numbers. For instance see below. I have two groups of raw data. If I perform my positive/negative calculation in the source data, I get differnt sub-total results than if I perform those calculations on the sub-totals themselves. I need the values shown in the Outside Pivot Calc Value column.

[tt]
[b]Group A[/b]
Raw Value  Pivot Calc Value  Outside Pivot Calc Value
 10             10                                   )
(15)             0                                   ) 
  2              2                                   )<-- Raw values in source table.
 22             22                                   )
( 5)             0                                   )
----           ---
 14             32                       14           <--- Total per pivot table

[b]Group B[/b]
Raw Value  Pivot Calc Value  Outside Pivot Calc Value
 20             20                                   )
(55)             0                                   ) 
  5              5                                   )<-- Raw values in source table.
 10             10                                   )
( 5)             0                                   )
----           ---
(25)            35                       0           <--- Total per pivot table

[/tt]

 

[SkipVought]

"...whether the sum is positive or negative. Not the individual numbers."

Are you kidding???

Check your math!!!

Using YOUR DATA, calculation done in the source table...
[tt]
Data
Grp Sum of Val Sum of POS
A 14 34
B -25 35
Grand Total -11 69

[/tt]


Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[combo]

When you perform calculations in pivot table with calculated fields, you always work with subtotals based on raw data - pivot table will apply formulas after grouping.
So the best would be to redesign the data with additional column as Skip suggested initially. If necessary, use ms query or ms access and work with external data. The problem could be if you have variable grouping rules (other than Day+Time only) for MAX function input.

combo