I'm not sure how to word my request, other than showing what I want to accomplish usinf an example (see attached Excel spreadsheet with data). My data consists of 2 columns, year and site. If I filter on the year, I do not want the Grand Total for that specific year rather I want all the Grand Total for all years in which a specific site appears. I've attached the spreadsheet that contains the data. I appreciate your consideration of my question.

(My examples containing tables did not display in text. Examples are provided in attached excel file.)

Attached Files

Manipulate data in pivot table.xlsx (16.4 KB, 10 views)

Sample sheets are usually the best way to start off.

But let me see if I understand. Rather than 5, 2, 3 that the filtered pivot gives for total now, you want 6, 10, 8 which are the totals for the unfiltered report. Is that right?

Since you are filtering for year, e.g. 2015, the other data are effectively invisible so I don't think you can get a total like that with the pivot. However, there may be more than one way to skin that cat.

Thank you gebobs. You are correct. I would like to display 6, 10, 8 when I select year 2015. In my subsequent query, I'd like to filter for year 2016 and but not include site 1Y (row 1Y removed) since it was already included in year 2015.

As for sample sheets, I tried to include them as tables in the text of this message but tables do not retain the formatting (I think). I did not submit as an image because I do not know how to link the image to a url. So I was stuck sending the spreadsheet as an attachment with the samples illustrated in the spreadsheet.

What are you trying to accomplish here? There may be other ways to do what you want but probably not with pivot tables.

Thank you for your patience. Here's my attempt at a written explanation:

Each row is an instance of a valve test requiring performance at a site. Currently the testing is very haphazard. The goal is to combine all valve testing for one site in a single year. The testing can be moved ahead (from 2017 to 2015) but testing cannot be postponed. I then want to be able to distribute the testing relatively evenly among a 5 year test interval (approximately 14 per year using the sample data in the attached spreadsheet). Because distributing the testing will take some finagling and shifting things around, I wanted a simple (but elegant) way for my boss to look at the data to pick and choose how he wants to consolidate the testing.
Of course, my actual dataset is much larger than the one provided.

My not so elegant solution: I filtered all rows by year 2015. I then copied the list of filtered sites in year 2015 and pasted on another sheet; selected 'remove duplicates'. I then had a manageable list of sites. I went back to my spreadsheet, removed the 2015 year filter. Then I filtered by site, selecting the sites I retrieved by removing duplicates. I then selected visible cells (F5, special) and applied a text color for all visible rows. I then selected sites with automatic color, then filtered with year 2016, got my list of sites, and applied a different text color. By using this approach, I can probably make do.

Very good. Always more than one way to get around a problem!