Forum Discussion
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.
Sergei switched the conditions. Here is a sample workbook with my formula and the corrected version of Sergei's formula.
13 Replies
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.
- 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 tried entered your solution exactly as stated, it also returns nothing but zero.
Sergei switched the conditions. Here is a sample workbook with my formula and the corrected version of Sergei's formula.
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))- It just returns a zero, every COUNTIFS function I've tried just returns a zero.
