 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008Subtotals in Pivot Tables
I've got a spreadsheet with a number of columns and I could do with putting a subtotal into a column in the middle of the data. Currently the columns are: Charged - Location - Size - Item Every line in my source data has a value in the Size column which is the same for each Location so that if I delete a line I don't end up without a Size for a Location.... but I want to be able to total the Size for each Charged and currently all I can do is get it to count every Size for every Item which gives me the wrong answer. So for instance if I've got data of: Charged - Customer A Location A Size 1.6 Item A Location A Size 1.6 Item B Location B Size 1.6 Item A Location B Size 1.6 Item B What I need to end up with is a Pivot that tells me that Customer A has a Size of 3.2 (because Location A and Location B are both 1.6) but currently all I can get is that there are 4 items and so the total is 6.4. Does that make sense? Can anybody assist? Anybody?! It'll save me hours of work doing something else.... > I've got a spreadsheet with a number of columns and I could do with putting a > subtotal into a column in the middle of the data. Currently the columns are: [quoted text clipped - 18 lines] > > Does that make sense? Can anybody assist? Hi Add another column to your source data, titled Size2 with this formula =IF(SUMPRODUCT(($B$2:B2=B2)*($D$2:D2=D2))>1,"",C2) Extend the data range of your PT to include this new column. Use Sum of Size2 as your data item, in place of Size and you should get the result you want.  SignatureRegards Roger Govier > I've got a spreadsheet with a number of columns and I could do with > putting a [quoted text clipped - 20 lines] > > Does that make sense? Can anybody assist? Thanks Roger. I think that does exactly what I want it do. Not sure how it works though! > Hi > [quoted text clipped - 29 lines] > > > > Does that make sense? Can anybody assist? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.