hi,

create a new table
F2: F5 put the values €‹€‹(A, B, C, D)
G1: Q1 put the values €‹€‹(2011, 2012, 2013, etc ...)
in cell G2 put the following formula:

=SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($B$2:$B$11)=G$1)*(13-MONTH($B$2:$B$11))*($D$2:$D$11))
+SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($C$2:$C$11)=G$1)*(13-MONTH($C$2:$C$11))*($D$2:$D$11))

and copy this formula in range G2:Q5


--
isabelle



Le 2011-11-16 18:11, via sarpi a écrit :

Your question needs better clarification.

sorry for this.

So I have 4 real estate projects, for each of them there is a lease in
place, where a tenant pays a rent.
The problem is that this rent might change at any date, within the
year. In the example above, for the project A, tenant pays 1800m from
1/11/2011 to 31/10/2012. This means that in 2012 tenant pays for the
first 10 months= (1800/12)*10. Then for the last 2 months of 2012 rent
steps up to (2200/12) per month (second row in the example above).
And so on.
Rent might change/step up without any fixed rule. I need a formula
which gives me the total paid for each project in a specific year. So
the formula would check how many months for the year in question
tenant pays a specific bracket (in the example above, for 2012 tenant
pays first bracket for 10 months, and second bracket for 2 months) and
sum the result. I believe is a sort of Sumproduct IF the bracket falls
within the year I am summing up, or something like this.

Hope this is clearer now
thanks a lot in advance
cheers