Rent Roll Help!

elmsley4

New Member
Joined
Aug 18, 2006
Messages
25
I feel like an idiot b/c I think this shouldn't be too hard:

I have a table for an office building showing tenants and lease expirations.
I want to show, as a percentage, the amount of tenants leaving in any given year.

Layout:
Column A = Tenant Name
Column B = Lease Start*
Column C = LEASE END*
Column D = SQUARE FEET
Column E = BASE RENT

*Dates are entered as follows: 1/15/2005

I can do this with a funky SUMIF feature (for Sq.Ft and Base Rent calculations), but isn't there something easier?

I want to know:
1. TOTAL Sq.Ft expiring for years 2008 - 2015 and,
2. TOTAL Base Rent expiring for years 2008 - 2015.

Everything else will be easy to calculate. Is there a SUMIF command that will display all leases expiring ONLY in 2009, 2010, 2011, etc...???

Thanks for any help!

-JRG
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try using SUMPRODUCT, e.g. for total base rent expiring for 2008-2015

=SUMPRODUCT((YEAR(C2:C100)>=2008)*(YEAR(C2:C100)<=2015),E2:E100)
 
Upvote 0
Clarification on post

Sorry I wasn't very clear. :)

I need to know ONLY the amount of SF expiring in 2008, and in the next cell, ONLY amount of SF expiring in 2009, then ONLY the amount of SF expiring in 2010, etc...

I don't need to know ALL the leases expiring from 2008 - 2015 as a lump sum, I would just sort the data by leases expiring and add the appropriate cells.

Thanks for the help!!!

-JRG

Is there a CSE formual I need to use in order to have the formula return the total
SF expiring >2008 but <2009?

Also, do I need to convert the format of dates expiring to just a YEAR format? (e.g. 1/15/2008 needs to be converted to 2008)???
 
Upvote 0
If you use SUMPRODUCT it's just a regular formula, you don't need CSE....and you don't need to convert the dates.

List the years in, say, G2 down then use this formula in H2 copied down for a sum of rent for leases expiring each year

=SUMPRODUCT(--(YEAR(C$2:C$100)=G2),E$2:E$100)

change ranges as necessary
 
Upvote 0
Hi elmsley4:

Following is a solution using EXCEL's one-variable DataTable ...
Book1
ABCDEFGHI
1TenantLeaseStartLeaseEndSFTRentdatabase
2T_36/15/20076/14/200812001500FALSEcriterion
3T_43/1/20082/28/201120002500DataTable
4T_57/9/20087/8/200912001500DataTable Solution
5T_611/16/20082/28/201020002500LeaseExpireYearSFTRent
6T_73/26/20097/8/201212001500200812001500
7T_88/3/20092/28/201320002500200912001500
8T_912/11/20097/8/201212001500201020002500
9T_104/20/20102/28/201520002500201120002500
10T_118/28/20107/8/201412001500201224003000
11T_121/5/20112/28/201520002500201320002500
12201412001500
13201540005000
Sheet3


computed criterion formula in cell G2 is ... =YEAR(C2)=$G$3

formula in cell H5 is ... =DSUM($A:$E,4,$G$1:$G$2)

formula in cell I5 is ... =DSUM($A:$E,5,$G$1:$G$2)

DataTable Range is ... G5:I13; column_input cell is $G$3
 
Upvote 0
This is awesome. I hate to say it, but JUST when I feel I'm getting pretty good w/ Excel, somoene like you makes me feel like a kid! (which is a good thing).

Thanks for the help. I don't fully understand how/why the above formulas work together, but I can appreciate the efficiency of the model.

Thank you again!

Best,

Joel
 
Upvote 0
Quickly,

How do you have H5 & I5 contain text (SFT & Rent) AND an equation (DSUM)????

This isn't a text box....
 
Upvote 0
Quickly,

How do you have H5 & I5 contain text (SFT & Rent) AND an equation (DSUM)????

This isn't a text box....

Hi elmsley4:

H5 and I5 indeed house the DSUM formulas that are needed for the one-variable DataTable. And no there are no TEXT Boxes, cells H5, and I5 are CustomNumber Formatted to display "SFT", and "Rent" (to optionally delineate more meaningfully the headers for the respective columns of the subsequent rows of the DataTable.)

Please look into the use of EXCEL DataTable to fully understand the entire setup for this proposed solution.
 
Upvote 0
I am going to ask sort of the same question, for the same type of spreadsheet: I have a sheet containing the same basic data (square footages in Column C, Expiration Dates in Column E). But, I want to be able to see how much square footage is expiring within 6 months of TODAY, 12 months of TODAY, etc., with the total SF for each date range summing into one cell. Bonus points for conditional formatting that will highlight the names of tenants (in Column B) whose lease are expiring in those ranges.

Thanks!

C.
 
Upvote 0
Quickly,

How do you have H5 & I5 contain text (SFT & Rent) AND an equation (DSUM)????

This isn't a text box....

Hi elmsley4:

Today I was prompted for solution to a followup question after a long time since 2008 vintage of this thread for which I Used Google spreadsheet's QUERY function ... and I thought I will revisit this question using the QUERY function ... and I have presented that solution in my following blog post

yogi_Compute Year By Year SquareFootage And Rent For LeaseEnd Year
Cloud Computing -- Google Docs Way: yogi_Compute Year By Year SquareFootage And Rent For LeaseEnd Year

so please check it out to see how this works for you.


Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top