Counting and specifying dates that fall within a predetermined range

[devdevdev51]

Hello,

I have a table of dates corresponding to a certain ID. Each ID has a start date and an end date associated with it, and I need only the dates that fall within that start and end. An example of what a small part of my table would look like is the following:

example excel table.png

(I have attached the excel document in case you are unable to view images)

Example Excel Table.xlsx

I need a formula that I can then apply to a large amount of data in order to know the number of events falling into the specified date range for each ID, and the earliest date that falls into the specified range.

Thank you for your help!
Devin

[Pete_UK]

Many contributors (including me) are not able to view .png files on this forum, due to incompatibilities with some browsers. Please attach a sample Excel workbook instead, otherwise your thread means nothing to us.

Pete

[devdevdev51]

I apologize. I have edited the original post. Thank you.

Devin

[FDibbins]

If I understand you correctly, try this in E4 and copy across...
=COUNTIFS($B:$B,"<="&E$2,$B:$B,">="&$E$3)

[JohnTopley]

Try

=SUMPRODUCT(($E2:$G2>=$B2)*($E2:$G2<=$C2)) #event

=MIN(IF((E2:G2>=B2)*(E2:G2<=C2),E2:G2)) earliest event

Enter the latter with Ctrl+Shift+Enter

[devdevdev51]

If I understand you correctly, try this in E4 and copy across...
=COUNTIFS($B:$B,"<="&E$2,$B:$B,">="&$E$3)

If I understand you correctly, try this in E4 and copy across...
=COUNTIFS($B:$B,"<="&E$2,$B:$B,">="&$E$3)

I don't think I explained myself correctly, but your equation was helpful.

I pasted the following in I2 and copied down, which gave me the correct values of "2" in I2 and "3" in I3, representing 2 dates falling within the first range and 3 dates falling within the second.
=COUNTIFS(E2:G2,"<="&C2,E2:G2,">="&B2)

So the first problem is solved! Thank you. Does anyone know how I can display the earliest date in column J?

Thanks,
Devin

[devdevdev51]

Try

=SUMPRODUCT(($E2:$G2>=$B2)*($E2:$G2<=$C2)) #event

=MIN(IF((E2:G2>=B2)*(E2:G2<=C2),E2:G2)) earliest event

Enter the latter with Ctrl+Shift+Enter

Your solution worked perfectly! I appreciate the help everyone. I will mark the thread as solved.

Thank you!
Devin

[FDibbins]

Glad you got what you needed, thanks for the feedback