Help on SUMIFS and COUNTIFS

Hi,

I am working on a lease spreadsheet that I have attached in this document.

These are my two problems:

First-->In the "Total Area" row in the lower table I created a SUMIFS function to calculate how much area each lease is taking up based on the year that the lease expires. This function works well for the leases that expire in a specific year, but for the lease that is month to month (MTM; where there is nothing in the "Lease To" column in the upper table) I had to subtract off cell E20 in order to get the correct value in cell I26. The idea is that I could paste a different property with different amounts of leases into this template and have the bottom table automatically update the new information. I have tried this and it works mostly, but the MTM lease never works out because of that subtraction of cell E20. Therefore I think instead of subtracting off E20, I need a COUNTIFS function to make sure the formula only includes the area from the MTM lease.

Second-->The % Area row in the lower table shows how much of the total area the leases that expire in a given year take up. The formula in that row for all of the expiration years works right now, but since I have the denominator (E20) locked in with $$ it doesn't work when I paste a different property in that has a different amount of leases (i.e., "Total Current" area is not located in cell E20 anymore). Therefore, I think I need the denominator for that formula to be a COUNTIFS(?) function that ensures that the denominator is the "Total Current" area for that property (which, in this case is located in cell E20)


("Total Annual Rental Rev", "% Revenue" and "Avg. Rent PSF" can be ignored for now.)

Any help on modifying these 2 formulas would be extremely helpful.