Exclude Some Row Labels from Pivot Table Calculation

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

jessiew84

Thread Starter
Joined
Jun 24, 2010
Messages
23
Hi all.

I have an extensive workbook that I'm trying to work out a few kinks in for 2011.

Currently I have a master data sheet that then filters through pivot tables to give more simplified information. Each pivot table contains the same row labels and values, but for different time frames. I have it set up to calculate some of the formulas for me automatically but there is one field that I need to exclude two of the ten row labels from. Currently I have a column next to the pivot table that I have calculating this weird figure but I'm wondering if there's a way I can have it be integrated into the pivot table in the event we add more row labels in the future.

Is there a way to set up the pivot table formula to exclude a certain row label?

I've included an example workbook. Let's say I'm trying to show the Grand Total for % of Cake Slices for Mon-Fri only. Is there a way to do this?
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
Normally you would just click on the dropdwon arrow for row labels and theres a set of tick boxs - so you can exlude certain row values - ie Sat & Sun
I only have excel 2003 - so i cant use the version you posted as i need to have a later version to test
 

jessiew84

Thread Starter
Joined
Jun 24, 2010
Messages
23
Etaf - unfortunately unchecking one of those boxes will exclude the unchecked for all calculations. I only want to exclude one item from one of the six pivot table calculations.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
sorry I dont understand - you want to exclude sat and sun in the calculations

what else do you need to exclude

you only have one PVT table - when you create six are you using the same records/data area - often you are asked to do this to save space - what happens if you do not do this

I have had numerous PVT tables and chart showing different data from the one data sheet - but i think i ran into trouble when i kept / based on the same data
 

jessiew84

Thread Starter
Joined
Jun 24, 2010
Messages
23
For the example I posted, I need to show the total of cakes, the total of guests, etc. all for Sunday - Saturday...but only on the % of Cakes column do I not want to include the weekend. I have to have multiple pivot tables to show different time periods of the total data. (ie. The week, the month, the quarter, the year).

Essentially it currently looks like this:



...but I would like for column F to not include Sat and Sun in the pivot table calculation. I can manually create a column, but if an item in column A is removed or one is added, it will not automatically add it to the manually created column.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top