Share via

Need help with Excel Formula - Rent Roll - Lease Rollover Analysis

Anonymous
2014-09-17T17:09:55+00:00

Good afternoon,

I am trying to create a lease rollover analysis template for my company that is based on information reported in a commercial real estate rent roll. 

The rent roll includes these columns:

Tenant Name         Occupied Sq Ft         Lease Expiration     

John's Place               1,500                        9/30/15

Steve's Place              2,500                        4/30/16

Bill's Place                  1,750                        3/31/17

My lease rollover schedule has the following columns

Lease Exp. Year            Sq Ft Expiring (this is the column I'm trying to solve)     

       2014

       2015

       2016

       2017

So what I need is a formula that will calculate the total amount of Sq Ft Expiring in each year, based on the information entered into the rent roll.  I can't figure out the formula to accomplish this though, as I don't know how to make it look for a certain date range, then calculate the Sq Ft associated with a lease that expires in that year.

Any help is appreciated!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2014-09-17T17:33:51+00:00

    Assuming the occupied Sq ft is in column B and the expiration in column C of Sheet 1 and your Lease Exp Year is in column A

    =SUMPRODUCT(Sheet1!B1:B3,--(YEAR(Sheet1!C1:C3)=A2))

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2014-09-17T22:31:10+00:00

    Hi,

    Try this

    1. Create a Pivot Table
    2. Drag Lease Expiration to the Row labels
    3. Drag Occupied Sq ft to the Value area section
    4. Right click on the first date in the Lease expiration field and select Group > Year > OK

    Hope this helps.

    0 comments