Need to graph records that fall between a start and end date over time

Occasional Contributor

I have a data set where each record (case) has a start date (column B) and an end date (column D).  I want to know how many records (cases) were active in any given month so that I can chart active cases over time.  I've tried =COUNIFS but I can't seem to get it right, and I'm not sure it fits my use case either.

11 Replies


If you have month as the date entered in any cell, e.g. H1 and formatted as mmm-yy or so, you may use

=COUNTIFS(B:B, ">"&EOMONTH(H1,-1), D:D, "<="&EOMONTH(H1,0))


Let's say the start dates are in B2:B100 and the end dates in D2:D100.


Enter the first day of each month you want to consider in a series of cells, for example 1-Jan-2021 in M2, 1-Feb-2021 in M3, ..., 1-Dec-2021 in M13. You can format these cells as mmm or mmmm so that they display only the month, if you wish.

In N2, enter the formula




Fill down to N13.

It just returns a zero, every COUNTIFS function I've tried just returns a zero.
I tried entered your solution exactly as stated, it also returns nothing but zero.
best response confirmed by Excel1085 (Occasional Contributor)


Sergei switched the conditions. Here is a sample workbook with my formula and the corrected version of Sergei's formula.

It did work, I had to tweak it (it was a lot of data). Now I have a new problem...
A case that has no end date must also be counted, how do I do that?


Here is a new version.

I'm running into another issue, the data is over multiple years so that solution is counting everything.  I added a file so you can see the actual data set.  Thank you.



In the attached version, I have added the condition that column B is not blank.

Looks like that did it. Thank you so much!