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

[Excel1085]

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.

[#2]

@Excel1085 

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))

[#3]

@Excel1085 

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

=SUMPRODUCT(($D$2:$D$100>=M2)*($B$2:$B$100<=EOMONTH(M2,0)))

Fill down to N13.

[#4]

It just returns a zero, every COUNTIFS function I've tried just returns a zero.

[#5]

I tried entered your solution exactly as stated, it also returns nothing but zero.

[#6 · Accepted solution]

@Excel1085 

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

[#7]

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?

[#8]

@Excel1085 

Here is a new version.

[#9]

Thank You So Much!

[#10]

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.

[#11]

@Excel1085 

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

[#12]

Looks like that did it. Thank you so much!

[#13]

Can you Please help me with the same version to exclude blank start date and end dates

[#14]

@siluvaijosephvivek 

That is actually simpler. See the attached version.