Try using SUMPRODUCT, e.g. for total base rent expiring for 2008-2015
=SUMPRODUCT((YEAR(C2:C100)>=2008)*(YEAR(C2:C100)<=2015),E2:E100)
This is a discussion on Rent Roll Help! within the Excel Questions forums, part of the Question Forums category; I feel like an idiot b/c I think this shouldn't be too hard: I have a table for an office ...
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 by elmsley4; May 7th, 2008 at 06:25 PM.
Try using SUMPRODUCT, e.g. for total base rent expiring for 2008-2015
=SUMPRODUCT((YEAR(C2:C100)>=2008)*(YEAR(C2:C100)<=2015),E2:E100)
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)???
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
Hi elmsley4:
Following is a solution using EXCEL's one-variable DataTable ...
******** ******************** ************************************************************************>
Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
= A B C D E F G H I 1 Tenant LeaseStart LeaseEnd SFT Rent database 2 T_3 6/15/2007 6/14/2008 1200 1500 FALSE criterion 3 T_4 3/1/2008 2/28/2011 2000 2500 DataTable 4 T_5 7/9/2008 7/8/2009 1200 1500 DataTable Solution 5 T_6 11/16/2008 2/28/2010 2000 2500 LeaseExpireYear SFT Rent 6 T_7 3/26/2009 7/8/2012 1200 1500 2008 1200 1500 7 T_8 8/3/2009 2/28/2013 2000 2500 2009 1200 1500 8 T_9 12/11/2009 7/8/2012 1200 1500 2010 2000 2500 9 T_10 4/20/2010 2/28/2015 2000 2500 2011 2000 2500 10 T_11 8/28/2010 7/8/2014 1200 1500 2012 2400 3000 11 T_12 1/5/2011 2/28/2015 2000 2500 2013 2000 2500 12 2014 1200 1500 13 2015 4000 5000
Sheet3
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
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
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
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.
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.
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
Like this thread? Share it with others