need to determine how many records there are between 2 dates

[htonamie]

I have a workbook with approximately 1500 names of contractors, the dates their contracts started and the dates they are projected to be renewed. I'm trying to now chart how many contracts end in a given month. tried countifs but obviously I've not created the formula correctly. any suggestions?

 

[Khalid NGO]

Hi,
Can you post a sample file with same data?

 

[htonamie]

I'm trying to do a record count in tab 2 to show how many contracts expire in a given month based on the dates in tab 1

 

[Khalid NGO]

hi,
i have used:
=SUMPRODUCT(--(MONTH(CONTRACTORS!$E$2:$E$5)=ROW()-1))

and update your dates with real numbers.
Previously your dates stored as text.

 

[shrivallabha]

Having dates in correct format will help and is actually better.

If there's some reason for keeping format as it is then in cell B2 try following formula:
=SUMPRODUCT(--(MID(CONTRACTORS!$E$2:$E$5,5,2)=TEXT(MATCH(A2,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),"00")))
and copy down. AUG and SEP months are not in your list for some reason.

 

[Hui]

You could use in CONTRACT RENEW BY MONTH CHART!B2:
=SUMPRODUCT(--(MID(CONTRACTORS!$E$2:$E$5,5,2)=INDEX({"01","02","03","04","05","06","07","08","09","10","11","12"},MATCH(A2,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0))))

 

[Somendra Misra]

@htonamie ,

If you keep the dates in the same format, you can try below formula in B2 and copy down:

=SUMPRODUCT(--(MONTH(A2&1)=(--MID(CONTRACTORS!$E$2:$E$5,5,2))))

Regards,