Summarizing for Pivot Table

[Mase1]

Among other columns, I have a worksheet with Username and Hours. What is the best way to summarize Hours by Username for a Pivot Table? Thanks for any help!!

[Mase1]

I created a sample of the worksheet, and uploaded it if anyone has time to take a look.

[royUK]

ID in Rows, Hours in Values?

[Mase1]

The ID is more of an instance marker - A unique ID is given per submission. So I would be summarizing per username, and hours would be the value. I hope I'm not misunderstanding your question! Thanks for taking a look.

[NBVC]

See attached.

[Mase1]

See attached.

I apologize - I should have given more explanation of what my aim is. I'm trying to get the average number of hours per student. Since Excel doesn't support a unique count in pivot tables, I'm not sure how to summarize hours by username (I'm guessing that's the best thing to do) in order to get this figure.

[NBVC]

You can change sum to average by right-clicking the Sum of Hours Column and selecting Value Field Settings.

[Mase1]

You can change sum to average by right-clicking the Sum of Hours Column and selecting Value Field Settings.

But that will only give me the number of hours per instance of submission. In other words, if there are 5 submissions from 3 students totaling to 15 hours, it would calculate the average on 5 submissions instead of 3 students. I can get the average per submission, but I can't get the average per student. I hope this helps clarify a little, and thanks again for you on-going help!

[NBVC]

What are the expected results for the sample workbook you posted?

[Mase1]

What are the expected results for the sample workbook you posted?

I tweaked the sheet some and re-uploaded to give a clearer example.

Pivot Table
Column Label = Category
Row Label = School
Values = Average of Hours

This shows an average amount of "Service" hours for Kirksville College of Osteopathic Medicine" as 11. On the sheet, 2 usernames logged 3 instances of service (8 hours, 12 hours, and 13 hours). So what the pivot does is add the amount of hours (33) and divide it by the number of instances (3), which gives 11. However, I would like to be able to also get the average per username. Since "Plaird" appears twice in the "Service" category for a total of 25 hours, and Jnair appears once in the "Service"category for a total of 8 hours, the average would be 16.5 (33 divided by 2).

[NBVC]

I added another column to the database, column U with formula in U2:

=IF(COUNTIFS(B$2:B2,B2,L$2:L2,L2)=1,SUMIFS($R$2:$R$9,$B$2:$B$9,B2,$L$2:$L$9,L2),"")

copied down.

I included the column in the Pivot Table and got the attached.

You can reorganize it the way you want...

[Mase1]

Excellent! Thank you VERY much for the help...