# help with Sum in between dates

V

#### via sarpi

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

D

#### Don Guillett

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

V

#### via sarpi

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
cheers

D

#### Don Guillett

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
cheers

examples to dguillett1 @gmail.com

I

#### isabelle

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 :

I

#### isabelle

sorry,

you have to remove the second "13-"

=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)*(MONTH(\$C\$2:\$C\$11))*(\$D\$2:\$D\$11))

V

#### via sarpi

thanks really a lot....BUT it doesnt work.

take for example the Project "C".
your formula dont get it, since its start-year is before our
calculation date, and its end-date is after. This means the rent is
always the same in the years between START and END, but your formula
(Year = ...) don't consider it

I

#### isabelle

hi,

ok i see what you want to say
but i see no other solution than use a VBA macro,
is what you want to do ?

--
isabelle

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

V

#### via sarpi

Ron got it right!

thanks a lot!

V

#### via sarpi

Ron,

what's the point of using Transpose? doesn't the array have the same
dimension even without transposing?

if I get it properly, the formula Ron suggests basically creates a
monthly schedule within the formula itself. Pretty smart!