Pivot table is showing fields that meet all selected criteria, want the data that doesn't

STO-Productivity-Feb0314.xlsxI work in cancer research and I'm trying to find a way to show the amount of studies that staff have in total and their role on the study. I have a data table that contains many fields but the crux of what I'm having trouble with starts with the following:

When I begin with a pivot table I start by just selecting the all of the studies listed in the table and then the two roles that staff can have data or regulatory. Staff can do both roles or just one on a study. I put the roles of data and regulatory into the row labels and the studies that we work on in the values. The problem is that I now only see studies that the staff member works on that meet working on both roles and not the studies that they serve only one role.

Can anyone shed some light on this dilemma as it would vastly help.

UPDATE: Please see the attached spreadsheet to illustrate further. Ablorh is the employee that I have it focused on and it only shows the one study she has both the regulator and data roles. The 7 other studies that the employee works on in the data role do not show.

Pivot tables are not geared for exactly what you are trying to do with it. Pivot table logic for combining multiple filter criteria is limited. In your example, you want to find studies that use a specific person as a data manager OR a specific person as regulatory coordinator. However, Excel can only interpret that as a specific person as a data manager AND a specific person as regulatory coordinator. So you only see the studies where that person is listed in both roles.

You need to split this into two pivot tables to get what you want. See attached. Depending on your ultimate goal, you will have to change the spacing to allow for more rows and/or columns.

There might be other ways to get what you want by other types of formulas, or by VBA, but it would be helpful if you were to do a mock-up of what you would really like to see. Do you want to list each study, or just list counts for each person? Do you want one table that shows data for all the people who could be in either of those roles?

I've also included another table that might be what you're thinking of that uses formulas. It shows summary counts for each role, for each erperson. However, you have to start out with a list of all possible names that could be in either role. I built the list with Advanced Filter.

First thank you so much, I can't thank you enough for your reply.

To answer your question my ultimate goal is where I think you were headed in the formula portion you show in Sheet1. I would like to show the following:

Staff member name with total counts for regulatory and data which you show already. I would also like it to show the study names of the studies that each staff member has along with the inst. status (institutional status - whether the study is open or closed) to determine workload.

Can anyone tell me what is the best way to show or accomplish this, and thank you again!