Excel 2007 : Find Unique Item Count & Percentage w/ Pivot Table

Hi,

Please look at attached spreadsheet.

I need a calculated field that tells me % utilization. For example, AD has 2 users so a maximum of 16 hours for that day (2 users * 8 hrs). Right now only 12 hours has been assigned to the users so the AD role is 75% utilized.

I can't figure out how to do this. Any suggestions? Thanks.

Michael

Mike could be working on different projects.

"For example, you can run a user count by role"
How do I do this? I've been trying different combinations and can't figure it out. If I can get it to show the user count per role I'd be golden.

Originally Posted by mcmuney

Why is Mike listed 3 times as AD? If this is the same user, shouldn't he be listed only once? Doing it that way will allow you to pivot users by count, which could then be used to calculate % utilized.

For example, you can run a user count by role. So for AD, it'll show 2 count for users (currently showing 4). You can then apply the maximum hours, which is 2 count * 8, then sum actual hours by role, which is 12, then divide to get your 75%.

I'd recommend having the following columns:

Name
Role
Project
NameProject (you can concatenate Name & Project)

Then pivot Role and NameProject to get the user count. That should do it!

Originally Posted by myfootsmells

Mike could be working on different projects.

"For example, you can run a user count by role"
How do I do this? I've been trying different combinations and can't figure it out. If I can get it to show the user count per role I'd be golden.

don't quite follow. can you attach a spreadsheet?

btw, don't worry about the project field. can you actually use the spreadsheet i attached below?