Pivot Table - Totals and Subtotals

[gshock]

Need help!! Here's the scenario - have 3 columns 1st column is price, 2nd is quantity and the 3rd is amount (qty * price).

At the bottom, under the GRAND TOTAL ROW, Excel sums up every single column, how do you get rid of the sum for the 1st and 2nd column (leaving the 3rd column intact)???

P.S. - using Excel version 97.

Thanks,

 

[Doroth]

If I understand your email correctly, all you need to do is click on the "a" column under grand total and clear the cell's contents (select cell\edit\clear\contents). Do the same with column "b" and it should work.

The only problem I can forsee is that the creator may have used the a+b=c formula to get the grand total of the c column instead of just summing the c column itself. If that is the case, you will need to make sure to insert the corrected formula. For example, the configuratiion to get the total of a 6 cell column would be: =SUM(C1:C6). Good Luck.

 

[gshock]

Hi Doroth:

Maybe I should elaborate - The "GRAND TOTALS" are part of the pivot table, hence, you can not delete the "cell" to get rid of the totals under the 1st and 2nd (price & qty) column.

What I would like the pivot table to do, is when it does the "GRAND TOTAL", it does not total up the 1st and 2nd column (price & qty).

I know how to turn "off" the GRAND TOTAL all together - but trying to turn off only certain columns is a diffent story.

 

[xlbo]

AFAIK, with pivot tables, you either have totals or you don't. I don't think that there's an option for selective totals. What is it that you are trying to acheive - maybe there is a workaround ?? Rgds
~Geoff~

 

[gshock]

I just want the pivot table to calculate totals for certain columns without having to cut and paste the table; and then modifying it every time I select a different criterion.

I was hoping that the pivot table was similar to MS Access where you can define what columns you want totaled.

Cheers

 

[xlbo]

Yeh but why do you only want totals for certain columns.... what's the process ...what do you do with those totals ??
why can't the other totals be visible ?? Rgds
~Geoff~

 

[gshock]

For instance, if you have price in column 1, qty in column 2, and total (prc * qty) in column 3, it does not make sense to have totals for price, it may for qty, and definitely for totals.

Hope this helps.

Rgds,

 

[xlbo]

Change your total on price to be "Average" of price then - surely that makes some sense and is useful and relevant information.... Rgds
~Geoff~

 

[gshock]

Thanks for the help .

However, in this case, averages only work if you are dealing with one product (i.e. gasoline prices).

Can't compare apples with oranges.

Cheers.

 

[macrus]

This may help.

If you are only ever going to have the same number of columns in your pivot then I'd suggest removing the "Sum of Price" altogether from the Pivot.

Alongside the Pivot table you should make use of a formula such as this one to disply the price. You can label the column Price and simply refer to the pivot table values like this. I'll assume that your pivot table covers columns A:C. In column D try this:

=IF(AND(A3<>&quot;&quot;,A3<>&quot;Grand Total&quot,C3/B3,&quot;&quot

This formula can be copied down column D to cover ALL cells if necessary.

Macrus