Pivot table - how to show SUM values from "within" the pivot and not outside.

[eeps24]

I have a pivot table that is showing hours people worked in 3 months (april may june). From left to right I want to sum up all the hours of people worked (sounds simple). But I want to calculate the sum within the pivot table (in column E) and not with for example me manually typing =sum(B5:D5) outside the pivot table. Why?

1. if I do "=sum..." (outside my pivot) as you see in my F column, then as I bring in more names its going to overwrite my formula. I am only filtering on 3 people right now and will want to bring in more. I will have to manually redo my sum formulas all over. I want where I can add/remove as many people as I want and the sum of all the months show automatically without me having to do it each time I add/remove a person.

2. I also want to make a pivot chart based on the sum of column E once I figure out my first question above. I currently (not showing in the pic) have manually entered in my sums (with SUM) but as I filter my pivot (for other data as needed) it no longer sees my reference cells and messes up. Which is why I want me SUM data to show in the pivot.

thank you


pivot.PNG

[bhenlee]

Is modifying your raw data or pivot table columns not an option?

[MrShorty]

Under (2007 ribbon) Pivot table tools -> Options -> Options dialog -> Totals and filters tab -> Select "show grand totals for rows" check box. I assume the same command is in a similar location in 2010. If not, find where this dialog and option checkbox are located, and you should be able to get your desired grand totals.

[eeps24]

I do have "show grand totals for rows checked" but I dont see them at the row level. This would be a huge help if I can get that to work. I will modify what I ever I need to at this point.. depending how we go about it.


and by row I mean have the grand total count from left to right...not up and down.


----update

I just inserted a calculated field.. I think I am on the right page.. but how do I tell it to sum up the row?

[MrShorty]

We may need to see your actual file instead of a picture of the pivot table. I cannot recreate what your picture is showing without deselecting the "grand total for rows" option.

I have attached a very simple file, showing grand totals for both rows and columns. If you cannot upload a sample of your file, see if you can see what is different about yours and mine.

[eeps24]

yes that is exactly what I want to do, trying to upload my sheet but the upload button is acting weird (maybe I need to update my java)..when I get home i will upload the sheet... if you can please do it that would be great!!! should have it uploaded in a hour or two.

thank you

[MrShorty]

I know that this site's file uploader works with mixed success. I have always had success by clicking on Reply to Thread or Go Advanced to bring up the main post reply page, then click on "manage attachments" below the post editor. This should bring up the main file uploader that can be used to upload files.

[eeps24]

cant seem to upload....when i click the attachment button i get a small white box but doesnt show anything.

ill try to figure something out

[MrShorty]

Perhaps it is my script blocker, but I don't see an attachment button. Did you try my suggestion to go through the 'go advanced" button, then click on the "manage attachments" link?

[eeps24]

im in advanced but i dont see a managed attachments link...i see a paper click icon, isnt that what you used?

[eeps24]

worst i can share it via dropbox. ill try the attachment again. ill do the dropbox tomorrow early morning if it comes down to it

[eeps24]

duh i see managed attachments..should be uploaded my friend.

plz look at sheet 4 ... scroll down

[MrShorty]

I am not that skilled at pivot tables, so I do not understand why your pivot table is not including a Grand Total column like mine did.

One difference I see between your source table and my simple pivot is that your source table has each employee name as a field header across the top. Mine has "name" as a field header, and each employee is listed with each record. This means that I can put "name" as column label in my pivot table, but your "column label" field in the pivot table is blank (sum of values). This difference seems to be what drives the "grand total" column -- having something (other than the Excel generated "sum of values") to put in the column label field.

Unfortunately, this pivot table question is beyond my knowledge of Excel's pivot tables, so I will seek the assistance of those more knowledgeable than I.

[MarvinP]

Hi eeps24,

Your data could be a lot better. You should fill down those dates and then UnPivot the names. I did those two steps using Power Query with about 5 clicks of the mouse. I give you back a tab called BetterTable that should pivot easily. Now, you can install the Microsoft Add-In called Power Query and do it yourself whenever your data changes.

Here are the steps.
Open Power query,
Get the data from a Table
Fill down on the date column
Select the first 3 columns and Unpivot
Save the Power Query to a sheet.

I then took the data and did a Pivot on it. I can't see your picture but I think the data in the new format can do what you need. See the attached. Time to learn some easy Power Query steps?

Power Query Fill Down then Unpivot to Pivot.xlsx

[eeps24]

Thank you... It was tough to track my data..I usually like to have the names in one column but it would make tracking my other data difficult. So I had no option but to make them going across. I appreciate your help.

How about anyone else want to take a try at my issue?

Thank you

[MarvinP]

Hey eeps,

Play with the pivot table idea using the data on the BetterTable sheet. If you add rows to your original data the Power Query will work with all added or deleted rows. When you get into the format I've given you the problem is very easy (I assume). But while you have it like you "like" it is very hard to work with.

[humdingaling]

i used calculated field to achieve the LOOK of what you are after
but, it is not dynamic...you would need to create a new field should you change combinations of columns you are after

ps if your going to go to the effort of doing this...you might as well sum outside the pivot table

[eeps24]

Marvin - I think we posted at the same time lol...your "better table" does look very good (I will save a copy of this) but for now I think I will just my current work sheet because at some point I will hand mine off to someone and they will maintain it. In my sheet the user will only need to fill in the numbers, but on yours they will need to add a new row for each person when they worked... adding total (today hours in a day they worked), worked (worked hours), not worked (given extra hrs as bonus but not actual worked), and whether it was a weekday or weekend. I will have to look in to power query, I have heard of it, but never used it. Thank you so much for this information.

humdingaling - Awesome, thats what I was trying to achieve...however you mentioned its not dynamic .. thats what I wanted to do (dynamic). So are you saying there is no way correct to make it dynamic? Sucks because if a user decides to add a few people (moves the list to the right) it will wipe out the calculation (which I have a chart using those totals). I guess ill have to make the calculation really far to the right ...OR..i can just make the totals to the left of the pivot...hmmmmmm


i do have other questions about how to do things... I guess I should make new threads and keep this my current problem only.

[bhenlee]

can't attach the sheet but go to Calculated fields then put this in the Formula field:

=Lehnhoff+'Panchal ' +Saravanan

[humdingaling]

Sucks because if a user decides to add a few people (moves the list to the right) it will wipe out the calculation (which I have a chart using those totals). I guess ill have to make the calculation really far to the right ...OR..i can just make the totals to the left of the pivot...hmmmmmm

just have the sum formula on the left of the pivot table instead then if you choose to add people it will automatically have included those cells
its much better way of doing it

[eeps24]

just have the sum formula on the left of the pivot table instead then if you choose to add people it will automatically have included those cells
its much better way of doing it

yes, I think that is what I have to do. Thank you guys....thanks bhenlee but I want to avoid manually typing names.