sum of info in a range based on dates


R

Real esate guru

I'm creating a spreadsheet that shows expiration dates of property as well as
the square footage of each property. What I want to do is to find out the
future amount of sq. ft. that will be available as leases expire.

Here is what I'm looking at. . .

A B C D
E F

Lease Lease Total % of
1 Tenant Name Begins Expires SF Space
2 01/01/00 02/05/07 600 7% 0.00
3 01/02/00 01/02/12 450 7% 0.00
4 01/03/00 02/08/07 700 7% 0.00
5 01/04/00 02/05/08 500 7% 0.00
6 01/05/00 03/28/09 550 7% 0.00
7 01/06/00 01/06/10 600 7% 0.00
8 01/07/00 01/07/10 350 7% 0.00

My formula was suppose to use the lease expires range (Column D) and add the
SF amount (column E) for all leases expiring in a given year. The returned
values will go into a section that looks like this:

A B C

SF Available
41 Year to be Leased
42
43 Month-to-Month 0
44 2009 0
45 2010 0
46 2011 0

I would like the formula to work even if the years in column B change down
the road. Is there any formula that will work that will help add the SF
amount if the year in column D equals the same year given below in Column B?

Here are a couple formulas I've tried:

=SUMIFS(E8:E33,D2:D30,"<Date(B44,12,31)", D2:D30,">Date(B44,1,1)")

=SUMIF(D2:D30,"YEAR(D2)=B43",E14:E39)

=SUMIFS(E7:E32,D2:D30,">I42",D2:D30,"<I43") where I42 and I43 represent the
numeric value of Jan 1 and Dec 31 of a given year.

If someone can help. THANK YOU!
 
Ad

Advertisements

M

Mike H

Hi,

If i've understood correctly then this may work

=SUMPRODUCT((YEAR(D2:D34)=2007)*(E2:E34))
or
=SUMPRODUCT((YEAR(D2:D34)=H1)*(E2:E34))

with 2007 in H1


Mike
 
R

Real esate guru

This works great. One more question though. . . I want to be able to
calculate the sq ft on leases that are month to month. For the expiration
date, MTM would be entered instead of an actual date. When I enter the MTM,
all other formulas come back as #VALUE!. Any suggestions?
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top