Count Number of Projects in any Given Month

[BradleyB]

Hi,

I am new to the forum and need some help with an excel formula.

I was wondering if there is a way to count the number of projects that will be running in any given month based on the start date and the end date of a project.

I have tried the COUNTIFS function but cannot seem to get it to take into account 2 date ranges (i.e the project start and end date and the start and end date of Jan, for example)

What I need is for the formula to show how many projects will be running in each month for the next 12 months or longer.

Any help would be much appreciated.

Thanks,

Bradley

 

[Special-K99]

If

Column A is project start date
Column B is project end date
C1 is month number 1 - 12

Try

=SUMPRODUCT(--(C1>=MONTH(A$1:A$1000))*(C1<=MONTH(B$1:B$1000))

 

[BradleyB]

Thanks for this - I think it is exactly what I am looking for. How would you suggest adjusting the formula to ignore certain cells (i.e. blank cells)?

The range I am using is in a Pivot Table. Would this make any difference?

If

Column A is project start date
Column B is project end date
C1 is month number 1 - 12

Try

=SUMPRODUCT(--(C1>=MONTH(A$1:A$1000))*(C1<=MONTH(B$1:B$1000))

 

[Special-K99]

Blank cells?
SUMPRODUCT will only sum cells that conform to the condition.

You're saying the project start/end dates could be blank ? That may present problems with blank end dates

You cant have blank start dates surely?


Am not an expert on Pivot tables, you can but try.

UPDATE: I just realised if a project goes beyond year end it may not get picked up,e g. we want to know how many projects in December (month 12).
A project goes from 01/11/2017 to 31/01/2018. 12 is greater than 11, its will get picked up.
But second part of formula says AND 12 < 1 - so it wont get picked up.

I dont think this formula will work.
You probably need to incorporate the year so YYMM rather than MM.

Getting trciky and I dont have much time to spend on this...

 

[BradleyB]

The data source I am using is incomplete. - this is why there may be empty cells. We are looking to get it updated but in the mean time we need the formula to ignore the row (or project) completely.

I see what you are saying with the issues with the formula. Not to worry if you haven't got the time. I will continue to work on something and hopefully will find a solution.

Thanks for the help!

Blank cells?
SUMPRODUCT will only sum cells that conform to the condition.

You're saying the project start/end dates could be blank ? That may present problems with blank end dates

You cant have blank start dates surely?


Am not an expert on Pivot tables, you can but try.

UPDATE: I just realised if a project goes beyond year end it may not get picked up,e g. we want to know how many projects in December (month 12).
A project goes from 01/11/2017 to 31/01/2018. 12 is greater than 11, its will get picked up.
But second part of formula says AND 12 < 1 - so it wont get picked up.

I dont think this formula will work.
You probably need to incorporate the year so YYMM rather than MM.

Getting trciky and I dont have much time to spend on this...