Count or Rollup not producing desired result - what are we missing?

[Lauren_Johnson]

My team has a base where we track all our projects which is linked to a another base where we are doing all of our capacity and velocity tracking/forecasting. In the capacity tracking base, we have hours of work associated with each of our project types (e.g: Project Type A, Project Type B, Project Type C). Those project types are correlated to the same project type classification in our project tracker base. In order to accurately calculate the amount of time per project type, we want to have a field that references our project tracker base and counts the number of each type of project and automatically populates the field. In the image -
‘Current Projects’ column should be referencing our project tracking base and counting how many records match what’s listed in the ‘Engagement Type’ column. Then populating the appropriate number. This is currently all manual. We’ve looked through a bunch of documentation on roll-ups, counts, formulas etc. and feel like we’re missing something. Any brilliant ideas or experience with something like this?

[Databaser]

Hi @Lauren_Johnson and welcome to the community!

If I understand correctly, you have multiple projects, each with a certain project type, that can link to 1 or more activities. Those activities only have 1 engagement type. You want to know per activity, how many project types are linked to it per project type?

If I’m correct, you can use a conditional rollup field, where you choose “Only include linked records from the [projects table] that meet certain conditions”. In that condition, you can choose the project type you want to rollup. You can create a rollup field per project type.

Or, if you don’t need the numbers for anything else, you could maybe also use the pivot table extension.