Need Help with a complicated CountIF formula

[heresteve2]

Hello everyone, I am trying to do a countif formula in my workbook but can't get it right. (maybe there is a better option that countif?) I have a feeling I may have a lot of questions with this workbook in the coming days as my boss has me trying to create this new sheet.

Essentially what I am trying to accomplish is to get column D on the Leasing Goals sheet to tally the expected move outs for that week. It would need to do this by looking for two conditions to be met one the sheet renewal chart and the other as a comparison of two columns..

Those two conditions would be
1) column L on the Renewal Chart to be "NTV" and
2) The lease expiration listed on the renewal chart column I being in the week ending on the date in column A of leasing goals.

The weeks end on Sunday so any lease expiration that is listed as a NTV with a lease expiration falling that Monday to that Sunday I would like counted.

If it goes as planned LEasing Goals D3 and D4 would be 0 and D5 would be 3 D9 would be 2.

I hope that makes sense and I've attached the workbook so you can see exactly what I am talking about. If anyone needs more explanation I'd be happy to try to be more details.

I greatly appreciate the help this community provides! Thanks!