How do you create a SubTotal within a Pivvot?

[James bolton]

Hi,

I am really scratching my head here on how to accomplish what I thought would be a really simple task. I'm sure someone here will give me an answer that will have me kicking myself.

I am trying to create a pivot table of all my people's timesheet data to determine how long a person spent in each county. The Pivott Table Rows are COUNTY & PERSON NAME (sub grouped) Values are COUNT of DATES and SUM of HOURS.

Raw data is as follows;
ROW A (PERSON NAME) ROW B (COUNTY) ROW C (JOB CODE) ROW D (COUNTY) ROW E (DATE) ROW F (HOURS)

The problem is, there are hundreds of lines where people have spent time in the same place, on the same day, but for 2 different jobs. Say Joe Blogg's spent 3 hours on the 13th January in Buckinghamshire on Job A, and 4 hours the same day, the same place, on Job B.

When I do a pivot table, the total hours are correct but the count of date shows every line where that person in that county has a date, regardless of whether or not its the same. Is there a way to either count only unique dates on the pivot table, or create a subtotal of total Hours under each person - county - date ?

I feel like this should be simple but really struggling to come up with a solution, any help would be greatly appreciated.

Thanks

 

[Luke M]

Can you upload your workbook, or at least an example? A lot of times with PT questions, it's a matter of putting things in the right order. But it's hard to visualize the layout with just words.

 

[James bolton]

Thanks for your reply,

Please see attached example spreadsheet

As you can see from the pivot tables, the total hour is correct, however, they count all the days line by line, I need only a count of "unique" days.

As an example, if someone is in London on the 13/11/14 and splits 7 hours into 3 jobs, 13/11/14 appears on 3 separate rows, the total hours on the pivot table is correct at 7 hours, however the count of days is shown as 3, I need it to show 13/11/14 as a count of 1 and not 3.

 

[Luke M]

Hi James,

Take a look at the attached, which uses a Pivot of a Pivot. IF the layout isn't what you were wanting, can you make a "mock-up" by manually filling in some cells showing what the desired layout and totals would look like?

 

[James bolton]

Hi Thankyou

I never thought about creating a double pivot table but it proves highly effective, thankyou very much this works perfectly !

 

[Luke M]

Glad you liked it James.