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

--
__________________________________
HTH

Bob

"Real esate guru" <Real esate wrote in
message ...
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!