sum of info in a range based on dates

[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,D230,"<Date(B44,12,31)", D230,">Date(B44,1,1)")

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

=SUMIFS(E7:E32,D230,">I42",D230,"<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!

 

[Mike H]

Hi,

If i've understood correctly then this may work

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

with 2007 in H1


Mike

 

[Bob Phillips]

=SUMPRODUCT(--(D230<DATE(B44,1,1)),--(ISNUMBER(D230)),E2:E30)

 

[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?

 

[Bob Phillips]

What did you use?