Countifs between a Date Range

[CuriousBritt]

I have a list of projects that have a start and end date in an excel file. I want to find out how many projects were live for each month.

For Example, here is some data:

Project 1 started 11/1/2014 and ended 10/31/2015.
Project 2 started 12/1/2014 and ended 3/30/2015.

From that data, I need a formula to be able to find the live projects for each month. Below is what I would like to accomplish:

Live Projects for November 2014: 1
Live Projects for December 2014: 2
......
Live Projects for March 2015: 2
Live Projects for April 2015: 1

I've tried Countifs("start date", ">"&monthstartdatecell, "end date", "<"&monthenddatecell) but that doesn't work

[TABLE="width: 500"]
<tbody>[TR]
[TD]Project 1[/TD]
[TD]11/1/2014[/TD]
[TD]10/31/2015[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]12/1/2015[/TD]
[TD]3/30/2015[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]10/28/2014[/TD]
[TD]6/5/2015[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD]3/5/2014[/TD]
[TD]10/5/2014[/TD]
[/TR]
[TR]
[TD]Project 5[/TD]
[TD]1/15/2014[/TD]
[TD]1/15/2015[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Re: HELP: Countifs between a Date Range

Welcome to the Board!

Your formula will only return records that started and ended within the same month!
You want to count records where they started before the end of the month, and ended after the start of the month.
So you want this structure:

[COLOR=#333333]Countifs("[/COLOR][B][I]start date[/I]", "<=" & [B][I]monthenddatecell[/I], "[I][B]end date[/B]", ">=" & [B][I]monthstartdatecell[/I]) [/B][/I][/B][/B]