Count of Active Contracts between two dates

[DDT123]

Greetings! I have a spreadsheet which lists contract Effective Dates in column A, and contract Expiration Dates in column B. Column E lists dates (E1=1/1/2019, E2= 2/1/2019, and so on...)
I'm needing to create a chart so I can see a visual of how many ACTIVE contracts per month that we'll have over the next several years. Can anyone assist with the formula I'd put into F1?

Thank'ya in advance!!

 

[PeteWright]

I'm not quite sure what you want.
Below table gives an example how to calculate the number of active contracts between two dates.
Does this help you?

MrExcel.xlsx
	A	B
1	eff date	exp date
2	01/01/2019	12/31/2021
3	01/02/2019	07/01/2021
4	03/15/2022	03/14/2025
5
6	count act.	1
7	from date:	01/01/2019
8	to date:	12/31/2021
Sheet3

Cell Formulas
Range	Formula
B6	B6	= SUMPRODUCT(--(($A2>=$B$7) * ($A2<=$B$8) * ($B2>=$B$7) * ($B2<=$B$8)))

Cells with Data Validation
Cell	Allow	Criteria
B7:B8	Date	between 01/01/1970 and 12/31/2099

 

[PeteWright]

Sorry, I've had the function wrong.

Use this formula to determine contracts that are/were only active between the given dates:

= SUMPRODUCT(--((A2:A4>=$B$7) * (A2:A4<=$B$8) * (B2:B4>=$B$7) * (B2:B4<=$B$8)))

Use this formula, if you need all currently active contracts:

= SUMPRODUCT(--((A2:A4>=$B$7) * (A2:A4<=$B$8) * (B2:B4>=$B$7)))

 

[DDT~123]

Thank you for the response. I'm looking for a count of active contracts that fall between two dates (Columns A and B).
Column E lists the dates for the axis of my chart and Column F lists the counts of the active contracts. See example below...
Since my axis goes to year 2030, I'm needing contracts which expire on January 1, 2024 to not be counted as active contracts for February 2024 through 2030.

 

[PeteWright]

Alright, this should do it,

for Jan 2019 till Dec 2023 use this formula in column F, cell F2:

= SUMPRODUCT(--( ($A$2:$A$1048576<=E2) * ($B$2:$B$1048576>=E2) ))

and change the ranges $A$2:$A$1048576 and $B$2:$B$1048576 to match your data.
Then copy down the formula until Dec 2023.

For Jan 2024 to Dec 2030 use this formula in column F, cell F61:

= SUMPRODUCT(--( ($A$2:$A$1048576<=E62) * ($B$2:$B$1048576>=$E$61) ))

again, change the ranges to match your data and copy down the formula until Dec 2030

 

[DDT123]

That worked! Thank you!! But why the difference between the two formulas? For Jan 2024 to Dec 2030...

 

[PeteWright]

But why the difference between the two formulas? For Jan 2024 to Dec 2030...

That's because of your requirements:

... I'm needing contracts which expire on January 1, 2024 to not be counted as active contracts for February 2024 through 2030.

If I got you right, every contract beyond January 2024 should not be taken into account, hence the two different formulas.