How to ignore a column of numbers in Grand Total for rows

[Michael Lester]

Hi,

My pivot table has three "amount" columns for various products representing
1) 2012 sales
2) 2013 sales
3) 2012 collections

Product Category Amount
Bananas Sold 2012 $9,861.00
Grapes Sold 2013 $5,109.00
Nectars Paid 2012 $(3,350.00)
Bananas Sold 2012 $1,817.00
Oranges Sold 2013 $4,355.00
Pears Paid 2012 $(4,821.00)
Plums Sold 2012 $4,433.00
.....

The pivot table looks like this:

Sum of Amount Category
Product Sold 2012 Paid 2012 Sold 2013 Grand Total-Unpaid 2012
Apples 18,958 -17,958 7,440 1,000
.....

and the grand total includes 2013 sales.

I would like to keep 2013 sales in the pivot table, but exclude them from the Grand Total, which I would then rename as "Unpaid (overpaid) 2012."

So, in this example, I would like my grand total column to be 1,000, instead of the calculated amount 8,440.

Thanks,
MHL

 

[Michael Lester]

Hi, I am using an old version of Excel (2000).

I am having a problem with a Calculated Item in Pivot Table.
My data includes five fields:

Manager
Client
Project
Type
Amount

“Type” has three designations, which I use as column headings for the amount:

Billed 2012
Collected 2012 (always a negative number)
Billed 2013

Manager, Client, and Project are row headings.

The Grand Total column picks up the sum of Billed 2012 + Billed 2013 – Collected 2012.

I want only the difference of Billed 2012 and Collected 2012, but I want the Billed 2013 column to show in the pivot table.

My calculated field is Billed 2012+Collected 2012, which should give me thedifference of just those two fields.

This works if I only use “Manager” as a row heading, but if I add Clients, my calculated field duplicates all projects and all clients for each manager, and does limit it to each manager’s particular projects and clients.

Worse yet, if I add Clients AND Projects, the program stops responding, presumably because it has too much work to do by not limiting projects and clients to the corresponding manager.

Is there to do this?

Thanks,
Michael