Cumulative data based on expiration date fill cell

[charcoop14]

I have a spreadsheet that need to track the total sq ft active for each month/year until expiration based, when the contract expires I need it to fall off in the cell

Property	Sqft	Expiration Date
A	21,000	12/31/2021​
B	522	1/15/2023​
C	65,555	2/1/2023​
D	22,033	6/30/2022​
E	150,000	6/30/2022​
F	232,500	6/30/2022​
Need Formula to fill in months in colums
Property	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23
A	21,000
B	522	522	522	522	522	522	522	522	522	522	522	522	522	522
C	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555
D	22,033	22,033	22,033	22,033	22,033	22,033	22,033
E	150,000	150,000	150,000	150,000	150,000	150,000	150,000
F	232,500	232,500	232,500	232,500	232,500	232,500	232,500

 

[Toadstool]

Hi Charcoop14,

As long as the Property Id's are unique then SUMIFS will do it.
P.S. I note even one day of a month attracts the full value. i.e. it is not pro-rated for days in a month.

Charcoop14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Property	Sqft	Expiration Date
2	A	21,000	12/31/2021
3	B	522	1/15/2023
4	C	65,555	2/1/2023
5	D	22,033	6/30/2022
6	E	150,000	6/30/2022
7	F	232,500	6/30/2022
8
9
10	Property	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23
11	A	21,000
12	B	522	522	522	522	522	522	522	522	522	522	522	522	522	522
13	C	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555	65,555
14	D	22,033	22,033	22,033	22,033	22,033	22,033	22,033
15	E	150,000	150,000	150,000	150,000	150,000	150,000	150,000
16	F	232,500	232,500	232,500	232,500	232,500	232,500	232,500
Sheet1

Cell Formulas
Range	Formula
C10:P10	C10	=EOMONTH(B10,0)+1
B11:P16	B11	=SUMIFS($B$2:$B$7,$A$2:$A$7,$A11,$C$2:$C$7,">="&B$10)

 

[charcoop14]

Thanks @Toadstool

 

[Toadstool]

Thanks @Toadstool

You're welcome!