Sum by Hours under 36 hours in Pivot

[aftabn10]

Hi, I was looking for some help in regards to a pivot table that i am trying to put together and I have added a sample workbook that identifies the issues that i am having.

To summarise the dataset (Data Sheet) that I have shows me the number of jobs an agent has closed per week and per product along with the number of hours they have worked.

What I am trying to do is to try and work out the number of jobs an agent has closed per hour and I have managed to do this using a sumif formula, 1 for the whole week and 1 including the product. (shown on Data Sheet as Avg and Avg_prod)

The problem I have is when I pivot this data the sum of closed per hour will work individually by product (With Product Pivot) but if i unfilter the product the sum of closed per hour does not give me the answer i need. The problem is that the hours are doubled up and I dont no how i can fix this as an agent can not work more than 36 hours.

If somebody could please advise how i can resolve this. Would really appreciate it.

Thanks in advance.

[aftabn10]

Hi, i think adding a MAX value of the hours could do the job. Will try and see how i get on.