Dynamic lists to summarize

Hi,

I'm working with a constantly changing data set of thousands of individual resources (each its own row). Two columns in particular are needed for further analysis. In this case it's the "Department" and "Role" and the count of resources under each role
There is a set number of Departments but not a finite set of Roles. For instance, today under the operations department there may be 10 Operations analysts but tomorow there may be 2 new Operations Associates.

I know I can pull the data in with a pivot but is there a way to create another sheet that constantly updates the roles under each department like a pivot would?

Any suggestions?

Thanks

Attach an example and a mock-up of the results highlighted.

Thanks for replying Mandy

I've attached an example and will amend my question a bit.

The problem I'm facing is that my source data will be changing and therefore my pivot will be expanding as data comes in. I've created some add on columns after the pivot that allow me to use the pivot data as a base count and then add or subtract from that number As the pivot refreshes and new lines are added, how do I change the columns I created to change with the pivot? For instance, if there's a new role, then I will need to insert rows and change my subtotal formulas.

Thanks so much