SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2294665%22%20slang%3D%22en-US%22%3ENeed%20to%20graph%20records%20that%20fall%20between%20a%20start%20and%20end%20date%20over%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2294665%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20data%20set%20where%20each%20record%20(case)%20has%20a%20start%20date%20(column%20B)%20and%20an%20end%20date%20(column%20D).%26nbsp%3B%20I%20want%20to%20know%20how%20many%20records%20(cases)%20were%20active%20in%20any%20given%20month%20so%20that%20I%20can%20chart%20active%20cases%20over%20time.%26nbsp%3B%20I've%20tried%20%3DCOUNIFS%20but%20I%20can't%20seem%20to%20get%20it%20right%2C%20and%20I'm%20not%20sure%20it%20fits%20my%20use%20case%20either.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2294665%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2294714%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20graph%20records%20that%20fall%20between%20a%20start%20and%20end%20date%20over%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2294714%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037245%22%20target%3D%22_blank%22%3E%40Excel1085%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20month%20as%20the%20date%20entered%20in%20any%20cell%2C%20e.g.%20H1%20and%20formatted%20as%20mmm-yy%20or%20so%2C%20you%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS(B%3AB%2C%20%22%26gt%3B%22%26amp%3BEOMONTH(H1%2C-1)%2C%20D%3AD%2C%20%22%26lt%3B%3D%22%26amp%3BEOMONTH(H1%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2294781%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20graph%20records%20that%20fall%20between%20a%20start%20and%20end%20date%20over%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2294781%22%20slang%3D%22en-US%22%3EIt%20just%20returns%20a%20zero%2C%20every%20COUNTIFS%20function%20I've%20tried%20just%20returns%20a%20zero.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2294916%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20graph%20records%20that%20fall%20between%20a%20start%20and%20end%20date%20over%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2294916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037245%22%20target%3D%22_blank%22%3E%40Excel1085%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESergei%20switched%20the%20conditions.%20Here%20is%20a%20sample%20workbook%20with%20my%20formula%20and%20the%20corrected%20version%20of%20Sergei's%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2294925%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20graph%20records%20that%20fall%20between%20a%20start%20and%20end%20date%20over%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2294925%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037245%22%20target%3D%22_blank%22%3E%40Excel1085%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20new%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

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

@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.

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

@Excel1085 

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?

@Excel1085 

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.

@Excel1085 

 

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

Looks like that did it. Thank you so much!