Apr 26 2021 01:16 PM - edited Apr 26 2021 01:48 PM
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.
Apr 26 2021 01:24 PM
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))
Apr 26 2021 01:27 PM
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.
Apr 26 2021 01:33 PM
Apr 26 2021 01:40 PM
Apr 26 2021 01:51 PM
SolutionSergei switched the conditions. Here is a sample workbook with my formula and the corrected version of Sergei's formula.
Apr 26 2021 01:52 PM
Apr 26 2021 01:57 PM
Here is a new version.
Apr 26 2021 01:59 PM
Apr 26 2021 02:56 PM - edited Apr 26 2021 02:59 PM
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.
Apr 26 2021 03:11 PM
Apr 26 2021 03:18 PM
May 19 2023 04:24 AM
May 19 2023 07:05 AM
That is actually simpler. See the attached version.
Apr 26 2021 01:51 PM
SolutionSergei switched the conditions. Here is a sample workbook with my formula and the corrected version of Sergei's formula.