SOLVED

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

Occasional Contributor

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

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

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

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

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

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.

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

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

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

I tried entered your solution exactly as stated, it also returns nothing but zero.
best response confirmed by Excel1085 (Occasional Contributor)
Solution

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

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

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

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?

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

Here is a new version.

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

Thank You So Much!

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

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.

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

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

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

Looks like that did it. Thank you so much!