Find a way to count the number of days worked via a pivot table?

[limaalpha]

I've got a dataset that lists records of resolved tickets by date and agent name (as well as a few other criteria).
I created a pivot table that has the agent names in the row field and the resolution dates in the column field so I have a daily count of number of resolved calls by agent as well as a month total per agent at the end of each row.

The resultant table is shows a blank cell on the days that the agent didn't work so I had hoped to figure out a way to get the pivot table to tell me how many days the agent worked that month as well. However even using calculated fields I cannot figure it out. Any help would be gratefully received.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3-Jul
[/TD]
[TD]4-Jul
[/TD]
[TD]5-Jul
[/TD]
[TD]6-Jul
[/TD]
[TD]7-Jul
[/TD]
[TD]8-Jul
[/TD]
[TD="align: center"]Grand Totals
[/TD]
[/TR]
[TR]
[TD]aaron
[/TD]
[TD]4
[/TD]
[TD]6
[/TD]
[TD]6
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD="align: center"]24
[/TD]
[/TR]
[TR]
[TD]mark
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]6
[/TD]
[TD="align: center"]26
[/TD]
[/TR]
[TR]
[TD]pablo
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD]7
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD="align: center"]24
[/TD]
[/TR]
[TR]
[TD]Grand Totals
[/TD]
[TD]17
[/TD]
[TD]10
[/TD]
[TD]13
[/TD]
[TD]8
[/TD]
[TD]10
[/TD]
[TD]16
[/TD]
[TD="align: center"]74
[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

If you use Count instead of Sum your grand total should show what you require. You can show both Count and Sum if you wish by adding the same field to the Values area of the pivot.

 

[limaalpha]

Hi steve the fish,
The pivot table is already set to count. It's showing the individual agent count of tickets solved on any given day each row, and the total count of tickets across all agents each day per column. The current grand totals column on the far right shows the number of tickets solved for the week rather than the sum or even count of days worked by each agent for the week which I think is what you were driving towards unless I misunderstand?

 

[steve the fish]

You do misunderstand yes because the grand total is summing not counting. Lets take Aaron. 4+6+6+3+5=24. So grand total is Sum. Count would show 5. Right click on one of the values inthe grnad total column. Then summarize values by. Change to count.

 

[limaalpha]

You do misunderstand yes because the grand total is summing not counting. Lets take Aaron. 4+6+6+3+5=24. So grand total is Sum. Count would show 5. Right click on one of the values inthe grnad total column. Then summarize values by. Change to count.

Hi steve the fish,
I'm not getting the expected result unfortunately. It seems the grand total column, while it is summing the number of tickets per agent each row, excel already is summarizing the values by count and if I change the total column to anything else then I change the daily and weekly figures too (they sum or count etc along with the grand total).
Any other ways you can think of to get the pivot table to count the days worked based on which days the agent doesn't have a ticket count in the table?

see screen print:

[/URL]******** async src="//embedr.flickr.com/assets/client-code.js" charset="utf-8">*********>[/IMG]