On Wed, 16 Nov 2011 08:23:07 -0800 (PST), via sarpi wrote:

Hi, I have the following numbers, about 4 real estate projects (named
A, B, C, D). For each of them, there is a lease contract, and the rent
changes according to a "start date" and an "end date".
But since I need to sum the rent paid in each year, I need for each
project the sum of rent paid in that specific year, considering the
rent amount can change during the year.
In the example below, project A would pay 2 months of first due rent
in year 2011, then in 2012 it will pay for 10 months the same rent,
and then the new rent for the next 2 months, and so on.
How to do these total?
thanks

Projects START END ANNUAl RENT
A 01/11/2011 31/10/2012 1.800.000
A 01/11/2012 31/10/2013 2.200.000
A 01/11/2013 31/10/2014 2.300.000
A 01/11/2014 30/12/2021 2.800.000
B 01/01/2012 30/09/2013 1.800.000
B 01/10/2013 30/12/2021 3.600.000
C 01/11/2011 30/12/2021 2.600.000
D 01/01/2012 31/12/2013 2.000.000
D 01/01/2014 31/12/2015 2.400.000
D 01/01/2016 30/12/2021 2.800.000

Assuming your table above starts in A1, NAME the ranges
Select the table
Defined Names: Create from selection / top row

You will then have four names like:
ANNUAL_RENT =Sheet1!$D$2:$D$11
END =Sheet1!$C$2:$C$11
Projects =Sheet1!$A$2:$A$11
START =Sheet1!$B$2:$B$11

Then, set up a new table:

I1: A
J1: B
K1: C
L1: D

H2: 2011
.... ...
H12: 2012

This formula must be **array-entered**:

I2:
=SUMPRODUCT((Projects=I$1)*(TRANSPOSE(
DATE($H2,ROW(INDIRECT("1:12")),1))=START)*
(TRANSPOSE(DATE($H2,ROW(
INDIRECT("1:12")),1))<=END)*ANNUAL_RENT/12)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

Finally, select the cell and fill right to L2
Then select I2:L2 and fill down to I12:L12

This is what I get:

A B C D
2011 $300.00 $- $433.33 $-
2012 $1,866.67 $1,800.00 $2,600.00 $2,000.00
2013 $2,216.67 $2,250.00 $2,600.00 $2,000.00
2014 $2,383.33 $3,600.00 $2,600.00 $2,400.00
2015 $2,800.00 $3,600.00 $2,600.00 $2,400.00
2016 $2,800.00 $3,600.00 $2,600.00 $2,800.00
2017 $2,800.00 $3,600.00 $2,600.00 $2,800.00
2018 $2,800.00 $3,600.00 $2,600.00 $2,800.00
2019 $2,800.00 $3,600.00 $2,600.00 $2,800.00
2020 $2,800.00 $3,600.00 $2,600.00 $2,800.00
2021 $2,800.00 $3,600.00 $2,600.00 $2,800.00

Note that time periods before the first date in START for a given project are NOT included.