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
Click to expand...

Your question needs better clarification.
 
V

via sarpi

Your question needs better clarification.
Click to expand...

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
 
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
thanks a lot in advance
cheers
Click to expand...

Instead of recreating your project, just send this and your file with
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
 
R

Ron Rosenfeld

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
Click to expand...

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.
 
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,

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!
 
R

Ron Rosenfeld

Ron,

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

Yes, but you need to multiply each element in each array by the other; if you don't transpose then you will only do a one-to-one, rather than a one-to-all. Like multiplying two arrays of columns, vs an array of row by an array of columns.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I count the number of blank cells 0
Reverse Index Match 2
Counting # of entries for each month, in a specific status 3
Countif between Time stamps and have - value 0
Find then highlight in yellow 6
compare date range with date in columns and input data from another cell 2
Lookup and count all cells that fall between 2 dates 3
Calculating occupational sick pay entitlement 0

Top