Count Projects happening within the same month

Megan Cunningham

Hello! I am trying to count projects by person overtime (monthly) in order to show how many projects a person on my team has going on simultaneously.

I am using two date columns- Kick off date (KO) and implementation date (Imp Date) with this formula. So, I want to count everything that is happening in the month of march (for example). Some projects kick off in march, some end in march and some continue on through march and I want to catch them all.

=COUNTIFS({AM}, CONTAINS("person", @cell), {KO}, IFERROR(YEAR(@cell), 0) < 2020, {KO}, IFERROR(MONTH(@cell), 0) >= 6) + COUNTIFS({AM}, CONTAINS("Dennis Klotter", @cell), {KO}, IFERROR(MONTH(@cell), 0) <= 6, {Imp. Date}, IFERROR(MONTH(@cell), 0) >= 6)

When I check the formula to count from gantt view, the number is off. I think it is because some projects started the previous year. I'd love to create this formula so it holds up year over year and doesn't need manual changes to get data.

Thank you!

Stefan

Hello Megan,

just a question before talking about your problem. Can the inbuilt resource view help to show what projects a person is working in a given timeframe?

Now to your question. You have two floating arrays here.

People: Assuming, that over the years they will not always be the same

and

Timeframe: As time moves on ;-)

I don't think that fixing your formula will give you a lasting solution therefore I suggest something else.

Create a (people)sheet with a rolling 6 or 12 month timeframe and the names of all the people. In your sheet with the kick off dates you could add columns for the next 6 or 12 months. In the cells in those month columns formulas could check, if the month is between start and end date and if yes set a checkmark or whatever.

In the other sheet you could use cross sheet formulas to countifs for a person and every month, if the checkmark is set.

Hope this helps

Stefan

Stefan

Hello Megan,

just a question before talking about your problem. Can the inbuilt resource view help to show what projects a person is working in a given timeframe?

Now to your question. You have two floating arrays here.

People: Assuming, that over the years they will not always be the same

and

Timeframe: As time moves on ;-)

I don't think that fixing your formula will give you a lasting solution therefore I suggest something else.

Create a (people)sheet with a rolling 6 or 12 month timeframe and the names of all the people. In your sheet with the kick off dates you could add columns for the next 6 or 12 months. In the cells in those month columns formulas could check, if the month is between start and end date and if yes set a checkmark or whatever.

In the other sheet you could use cross sheet formulas to countifs for a person and every month, if the checkmark is set.

Hope this helps

Stefan

Megan Cunningham

Thanks for your help Stefan!