|
sum of info in a range based on dates
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! |
sum of info in a range based on dates
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 "Real esate guru" wrote: 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! |
sum of info in a range based on dates
|
sum of info in a range based on dates
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? "Mike H" wrote: 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 "Real esate guru" wrote: 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! |
sum of info in a range based on dates
What did you use?
-- __________________________________ HTH Bob "Real esate guru" wrote in message ... 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? "Mike H" wrote: 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 "Real esate guru" wrote: 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! |
| All times are GMT +1. The time now is 11:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com