Allocation of Amount, based on the Monthly amount and dates

[ExcelNewbie2020]

I would like to allocate the amount (column B) from the starting date (column D and range G2:R2) and monthly (column C)..
Please see yellow color for the expected output.. Many Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1							FORMULA HERE
2		allocated amount	Monthly	date start (dd/mm/yy)			31-01-24	29-02-24	31-03-24	30-04-24	31-05-24	30-06-24	31-07-24	31-08-24	30-09-24	31-10-24	30-11-24	31-12-24	TOTAL
3	item1	5000	1000	01-01-24			1000	1000	1000	1000	1000	0	0	0	0	0	0	0	5000
4	item2	3200	500	01-04-24			0	0	0	500	500	500	500	500	500	200	0	0	3200
5	item3	4000	2500	01-06-24			0	0	0	0	0	2500	1500	0	0	0	0	0	4000
Sheet2

Cell Formulas
Range	Formula
S3:S5	S3	=SUM(G3:R3)

 

[Snakehips]

@ExcelNewbie2020 Does this help?

AutoTraining.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1							FORMULA HERE
2		allocated amount	Monthly	date start (dd/mm/yy)			1/31/24	2/29/24	3/31/24	4/30/24	5/31/24	6/30/24	7/31/24	8/31/24	9/30/24	10/31/24	11/30/24	12/31/24	TOTAL
3	item1	5000	1000	1/1/24			1000	1000	1000	1000	1000	0	0	0	0	0	0	0	5000
4	item2	3200	500	4/1/24			0	0	0	500	500	500	500	500	500	200	0	0	3200
5	item3	4000	2500	6/1/24			0	0	0	0	0	2500	1500	0	0	0	0	0	4000
Sheet2

Cell Formulas
Range	Formula
G3:R5	G3	=IF(COLUMN(G$2)>=MATCH(EOMONTH($D3,0),$G$2:$R$2,0)+6,MIN($C3,$B3-SUM($F3:F3)),0)
S3:S5	S3	=SUM(G3:R3)

 

[steve the fish]

My take seen as ive done it:

=IF(G$2>=$D3,MIN(MAX(0,$B3-SUM($F3:F3)),$C3),0)

Column F would need to remain empty.

 

[ExcelNewbie2020]

@ExcelNewbie2020 Does this help?

AutoTraining.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1							FORMULA HERE
2		allocated amount	Monthly	date start (dd/mm/yy)			1/31/24	2/29/24	3/31/24	4/30/24	5/31/24	6/30/24	7/31/24	8/31/24	9/30/24	10/31/24	11/30/24	12/31/24	TOTAL
3	item1	5000	1000	1/1/24			1000	1000	1000	1000	1000	0	0	0	0	0	0	0	5000
4	item2	3200	500	4/1/24			0	0	0	500	500	500	500	500	500	200	0	0	3200
5	item3	4000	2500	6/1/24			0	0	0	0	0	2500	1500	0	0	0	0	0	4000
Sheet2

Cell Formulas
Range	Formula
G3:R5	G3	=IF(COLUMN(G$2)>=MATCH(EOMONTH($D3,0),$G$2:$R$2,0)+6,MIN($C3,$B3-SUM($F3:F3)),0)
S3:S5	S3	=SUM(G3:R3)

that works.. thanks man

 

[ExcelNewbie2020]

@ExcelNewbie2020 Does this help?

AutoTraining.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1							FORMULA HERE
2		allocated amount	Monthly	date start (dd/mm/yy)			1/31/24	2/29/24	3/31/24	4/30/24	5/31/24	6/30/24	7/31/24	8/31/24	9/30/24	10/31/24	11/30/24	12/31/24	TOTAL
3	item1	5000	1000	1/1/24			1000	1000	1000	1000	1000	0	0	0	0	0	0	0	5000
4	item2	3200	500	4/1/24			0	0	0	500	500	500	500	500	500	200	0	0	3200
5	item3	4000	2500	6/1/24			0	0	0	0	0	2500	1500	0	0	0	0	0	4000
Sheet2

Cell Formulas
Range	Formula
G3:R5	G3	=IF(COLUMN(G$2)>=MATCH(EOMONTH($D3,0),$G$2:$R$2,0)+6,MIN($C3,$B3-SUM($F3:F3)),0)
S3:S5	S3	=SUM(G3:R3)

sir, the formula works properly as expected.. can this be achieved by using a spill array formula?..