How do I count how many projects a team member is working if the project lasts longer than a month?

madeleine.roberts · 09/22/21

So, I'm trying to create a metrics sheet formula that will count the number of projects that occur in each month.

In my resource planning sheet I am using as a reference I have a Project column, Assigned To, Start Date, End Date along with a few others. The projects are divided by months and the resource planning sheet spans over the next two years. I am trying to count the number of projects that each team member is responsible for each month. On my metrics sheet I have a new section for each month. I am trying to use a countif function but it will not count the project for all 3 months. For example if the project starts 9/01/2021 and ends 11/01/2021 they need to have a count for working on a project under the September, October, and November section and it is only counting that the team member is working on one project and it is in September. How can I create a formula to recognize that if range of start date to end date occurs in October it would count it once as well, and once for November, and it would not count the team member for having a project for December because the end date is in November.

The formula I've been trying to use is:

=COUNTIFS({PMO Release Schedule/Resource Planning Range 1}, [Second Metric (If Needed)]@row, {PMO Release Schedule/Resource Planning Range 3}, MONTH(9))

Range 1 is the area of the Release Schedule Sheet that has who the project is assigned to. The second metric if needed is the team member's name that the project is assigned to, and the Range 3 is the area of the Release Schedule Sheet that has the project dates that start in September.

I keep getting an #INVALID DATA TYPE error and I cant figure out why. Also, the current function I have would not count the project if it occurred in any other month than the start date.

Any help at all would be greatly appreciated.

Genevieve P. · 09/23/21

Hi @madeleine.roberts

Instead of looking for the month, we'll want to search for the date range between your Start and End dates, using the DATE function.

For example, if the Start Date is less than or equal to the End of September, and the End Date is greater than or equal to the Start of September, then that row has a month in September (whether or not the September date is actually in either of these cells.

Each statement would look something like this:

{Start Date}, @cell <= DATE(2021, 9, 30)

{End Date}, @cell >= DATE(2021, 9, 1)

So then you can add this into a COUNTIF. Here's my example:

=COUNTIFS({Contact}, [Assigned To]@row, {Start Date}, @cell <= DATE(2021, 9, 30), {End Date}, @cell >= DATE(2021, 9, 1))

And change out the DATE for each month column you're looking at. For example, this is November:

=COUNTIFS({Contact}, [Assigned To]@row, {Start Date}, @cell <= DATE(2021, 11, 30), {End Date}, @cell >= DATE(2021, 11, 1))

The reason I did it this way is so that when you cross over into 2022 it will look across the correct months instead of just seeing the month number and ignoring the year.

So in your instance:

=COUNTIFS({PMO Release Schedule/Resource Planning Range 1}, [Second Metric (If Needed)]@row, {PMO Release Schedule/Resource Planning Range 3}, @cell <= DATE(2021, 9, 30), {End Date}, @cell >= DATE(2021, 9, 1))

Keep in mind you will need to add a new Range, Range 4 I suppose, as your End Date column. Let me know if this works for you and makes sense!

Cheers,

Genevieve

Genevieve P. · 09/23/21