Prorated Rent Schedule Formula Help
Need a formula to calculate what is due per calendar year when the rent amount changes year to year (midyear) Any help appreciated!
06/01/1905/31/20 Total Rent Due: $175,000
06/01/2005/31/21 Total Rent Due: $200,000
Total Rent of 2019:
Total Rent of 2020:
Comments

The easiest way would be to break it down into a few more sections.
6/1/19  12/31/19
1/1/20  5/31/20
6/1/20  12/31/20
1/1/21  5/31/21
Have these dates in two separate date columns ([Range Start] and [Range End] for this example.
In the next column, you would have the rent amount for that period ([Amount Per Year] for this example).
Since you are splitting the year in half, you can use another column ([Amount Per Period]) to take the yearly amount and divide it by two which will give you the amount due per period.
=[Amount Per Year]@row / 2
We will then use a Year column to manually enter the year you are looking to sum and an [Amount Due] column with the following:
=SUMIFS([Amount Per Period]:[Amount Per Period], [Range End]:[Range End], YEAR(@cell) = Year@row)
.
The [Amount Due] column will display next to the corresponding year, how much is owed for that calendar year. (See screenshot).
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 352 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!