Pivot Table Total is not Summing up a Selected Field

[bessie426]

Hello All,

My issue is with my pivot table on excel 2007. I have a large array of employees and the hours worked for a week. My goal is to get a daily count of the number of employees that worked that day per project.

I created a manual formula to create a new field on my pivot table (crew count) that places a value in that field if there are any values in the previous field (hours), which is derived from my raw data. The formula is =IF(Hours>1,1) and the correct output is produced in the field (crew count), however, the total at the bottom of the (crew count) field, which should give me the total number of employees for that project, only shows 1 on all of my totals for all projects. My "Value Field Setting" is on Sum but I can't figure out why it is not summing up my "crew count" field.

I wanted to post a screen shot of my pivot table but I am not familiar with how to do so. This is my first post on a forum ever. The extent of my knowledge in posting is to copy and paste. If anyone can give me on pointers to do that as well it might make my question easier to answer.

Thank you all so much!

 

[Comfy]

Without seeing your source data I can only speculate.

You don't need to create another column.

Just add your Crew column and in the field settings change SUM to COUNT.

If you have included the dates and projects in your Pivot you should then see a count of employees.

I wanted to post a screen shot of my pivot table but I am not familiar with how to do so. This is my first post on a forum ever. The extent of my knowledge in posting is to copy and paste. If anyone can give me on pointers to do that as well it might make my question easier to answer.

Thank you all so much!

The best thing to do would be to anonymise your source data and then paste a section here so we can see what headers are included and the type of data they contain.

You might also want to look at this: http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

Post #2

This will take your data in excel and convert it into HTML. It will then add it to your Window clipboard so that you can paste it into the forum.

 

[RoryA]

Unfortunately your calculated field calculates as:
=IF(SUM(Hours)>1,1)
at every level of the pivot table, including the Subtotals/Grand Total which are not simply a sum of the rows above. A calculated field won't work for this - you'd need to add something to your source data if possible.

 

[bessie426]

Thank you so much Comfy for your response.

I'm not able to just add another column to my pivot table for my crew count because an employee could possibly have their hours split into a few different categories for one project for one day. I only want them to be counted once and the pivot will count the number of occurences.

Thank you also for the link. I will have to try to figure it out. I'm a novice when it comes to computer stuff.

 

[bessie426]

Thank you RoryA for your response. I was hoping that I wouldn't need to add something to my source data because it's over 80,000 lines but I suppose I will have to do so in this case.

Thank you again!