Count based on specific quantity & criteria

I have data for multiple projects and each project has multiple resources assigned to it. Most projects have multiple resources with the same role working on it, however, there are multiple roles assigned and each will use a different quantity of hours to complete the work.

For this example I've only used 1 role. I have set up a pivot table and am able to get the list of names filtered by their role, and the hours worked. However, there are 100+ projects and I need the Project number for each project that has 3 or more resource of a specific role engaged.

For Example:
Project 1
PSE 1 - 8 hours
PSE 2 - 24 hours

Project 2
PSE 1 - 8 hours
PSE 2 - 24 hours
PSE 3 - 16 hours
PSE 4 - 8 hours

Project 3
PSE 1 - 8 hours
PSE 2 - 24 hours
PSE 3 - 16 hours

Note: PSE 1 may be a different person for each project, same for PSE 2, 3, etc. however, I have the specific Role defined in a column. It is the Role that I'm focused on, not the individual.

The output I'm looking for, based on the above example would be:
Project 2 has 4 PSE's
Project 3 has 3 PSE's

Project 1 would not be reported because it only has 2 PSE's.

Thanks!

I don't know if I understand what you're after, but I would try using a helper column.

In Col A, I added a formula =IF(LEFT(B2,4)="Proj",B2,A1) in A2 and copied down. (You could put this anywhere!)
This gave me the Project on every row.

I then assumed you have a list of said projects. Say in D2:D3.

This formula will give you the answer you want.
=IF((SUMPRODUCT(--($B$2:$B$15<>"")*($A$2:$A$15=D2))-1)>2,(SUMPRODUCT(--($B$2:$B$15<>"")*($A$2:$A$15=D2))-1),"")

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Reply, then Click on Go Advanced and click on the Paper Clip OR scroll down to Manage Attachments.

If this has been of assistance, please advise.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

David,
Sorry, I don't think my example was clear enough and I had trouble applying your proposed solution. I've attached a sample of the data, sorted by project number. What I need to determine is how many different resources from each ROLE worked on a project.

Column A is Project Number
Column B is Role
Column C is Resource Name (actual names have been replaced with Name 1, Name 2, etc.)

By setting up a Pivot table and manually counting I can see that 2 PSE's worked on the 1st, 2nd & 3rd projects, 7 on the 4th, 2 on the 5th, and 6 on the 6th project. However, I have 100+ projects and am looking for a formula to count how many of each resource type worked on a specific project.

Hope this helps.

Thanks,
Steve

Hi Steve,

Sorry for delay in getting back to you.

Back to the drawing board!

I have created a Pivot Table of your data using the Show in Tabular Form option. I also chose the Repeat all item labels option.

I then ensured I wasn't getting sub-totals and ended up with a PT showing columns for Project Number,Role and Resource Name

I then created a separate matrix showing each individual project with the Roles across the top (F3:L9).

Finally, I entered =COUNTIFS($A$4:$A$42,$F4,$B$4:$B$42,G$3) into G4 and copied down and across. That gives a matrix with the number of individuals used in every role for every project.

Of course, if you are only tracking PSEs you don't need the other Roles.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

David, I understand what you're doing and am familiar with the COUNTIFS function, but ended up with all Zero's. I'm missing something, but will double check my work. I understand the logic.

Thank you!

Thanks for the vote of confidence through the Rep.

Please let me know if you need further help. Post another sample showing what you have attempted, but getting zeros.

Regards,

David

David,
I was able to resolve it. It was an error on my part in the set up of the PT. Once I formatted it correctly the formula worked great.

Thank you for the help!
Steve

Good news. Happy to help!