Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 15th 12, 11:13 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2012
Posts: 2
Default How to ignore a column of numbers in Grand Total for rows

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

  #2   Report Post  
Old December 17th 12, 01:11 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2012
Posts: 2
Default Pivot Table Calculated Item Crashes Excel

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 the difference 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

On Saturday, December 15, 2012 3:13:50 PM UTC-8, Michael Lester wrote:
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



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
Format Total but not GRAND Total rows MikeF[_2_] Excel Programming 0 December 29th 08 11:04 PM
Format Total but not Grand Total rows MikeF[_2_] Excel Programming 0 December 29th 08 11:01 PM
Not display rows where grand total is 0 wacNTN Excel Discussion (Misc queries) 1 August 24th 06 04:28 PM
How can I total just the numbers in a column and ignore non-numeric strings? Bud Excel Worksheet Functions 1 June 30th 06 08:02 AM
Ignore values in a particular row for Grand Total Martyn Upson[_2_] Excel Programming 0 August 12th 05 10:38 AM


All times are GMT +1. The time now is 03:56 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017