Analyst Assigned Projects by Month

danno7263

Hi there,

Struggling with how to set up a formula that will how many projects an analyst is assigned to for the upcoming months. Source sheet has each project name, start and end date and assigned analyst. I'd like to populate a sheet showing each analyst with their # of assignments going out into March 2024. Report sheet would be set up with a column showing analyst name, then columns going out from July 2023 to March 2024. Not the best with the logic on the formulas, so any suggestions would be great! Thank you.

Matt Johnson

Hi @danno7263

Here are 2 ways to accomplish this:

1) Create a report, group it by assigned analyst and summarize it by counting Project Names. That will give you a nice visual of the analyst name and how many projects they have; and the ability expand and collapse each analyst name to dive into the project details. And you can set a copy of the report up with a filter "has any of" to be "current user". The current user or My Tasks report is then auto-filtered to be just the rows assigned to the person viewing it.

2) Create a metric sheet, list all of the analysts in one column, do a cross sheet countif formula in another column like: =COUNTIF(range-from-other-sheet, Analyst Assigned@row) Then you could display that range in a chart or table on a dashboard.

I hope that helps.

Matt