Automatically allocate the final paid date for the partially paid invoices

[arrud14]

Hii
I have a data set containing the invoice details and the details of recurring payment received from the customer and i want to allocate the final paid date for the every particular invoice calculating the sum of amount automatically

 

[arrud14]

In that case mark them both in your account details now ..
View attachment 82746


.. and in future when you ask a question if it needs to work in the older version, say so in the first post of the thread.

Done, Now pls help me sir even when i am using the same formula in excel 2021 it is showing #name error

 

[Peter_SSs]

Done,

Thank you.

Try this. Add an extra column to the payment details section as shown. You can hide that new column if you want.
Replacement formula for column F is suggested.

arrud14.xlsm
	C	D	E	F		H	I	J	K	L	M
2	Invoice Amount	Invoice No.	Names	Clearence date			Fos	Banking	Date	Bank	Cumulative by Name
3	2591	39453074	RAJAN PAL	06-Dec-22			CHANDAN	3300	08-Dec-22	PUNB	3300
4	5432	39468693	RAJAN PAL	06-Dec-22			CHANDAN	5950	11-Dec-22	PUNB	9250
5	4806	39460801	RAJAN PAL	10-Dec-22			CHANDAN	6000	12-Dec-22	PUNB	15250
6	2550	39503179	RAJAN PAL	10-Dec-22			CHANDAN	3900	13-Dec-22	PUNB	19150
7	3300	39542391	CHANDAN	08-Dec-22			CHANDAN	8000	14-Dec-22	PUNB	27150
8	3444	39566311	CHANDAN	11-Dec-22			RAJAN PAL	12100	06-Dec-22	PUNB	12100
9	2505	39566584	CHANDAN	11-Dec-22			RAJAN PAL	6082	10-Dec-22	PUNB	18182
10	2751	39574859	RAJAN PAL	10-Dec-22			RAJAN PAL	11421	12-Dec-22	PUNB	29603
11	3411	39608264	CHANDAN	12-Dec-22			RAJAN PAL	3711	13-Dec-22	PUNB	33314
12	4805	39608111	CHANDAN	13-Dec-22
13	2700	39625463	CHANDAN	14-Dec-22
14	3600	39611283	CHANDAN	14-Dec-22
15	4225	39603022	RAJAN PAL	12-Dec-22
16	2902	39603295	RAJAN PAL	12-Dec-22
17	4294	39603153	RAJAN PAL	12-Dec-22
18	3900	39685842	CHANDAN	not paid
19	3711	39671011	RAJAN PAL	13-Dec-22
Sheet3

Cell Formulas
Range	Formula
M3:M11	M3	=SUMIF(I$3:I3,I3,J$3:J3)
F3:F19	F3	=IFERROR(AGGREGATE(15,6,K$3:K$11/((I$3:I$11=E3)*(M$3:M$11>=SUMIF(E$3:E3,E3,C$3:C3))),1),"not paid")

 

[arrud14]

T

Thank you.

Try this. Add an extra column to the payment details section as shown. You can hide that new column if you want.
Replacement formula for column F is suggested.

arrud14.xlsm
	C	D	E	F		H	I	J	K	L	M
2	Invoice Amount	Invoice No.	Names	Clearence date			Fos	Banking	Date	Bank	Cumulative by Name
3	2591	39453074	RAJAN PAL	06-Dec-22			CHANDAN	3300	08-Dec-22	PUNB	3300
4	5432	39468693	RAJAN PAL	06-Dec-22			CHANDAN	5950	11-Dec-22	PUNB	9250
5	4806	39460801	RAJAN PAL	10-Dec-22			CHANDAN	6000	12-Dec-22	PUNB	15250
6	2550	39503179	RAJAN PAL	10-Dec-22			CHANDAN	3900	13-Dec-22	PUNB	19150
7	3300	39542391	CHANDAN	08-Dec-22			CHANDAN	8000	14-Dec-22	PUNB	27150
8	3444	39566311	CHANDAN	11-Dec-22			RAJAN PAL	12100	06-Dec-22	PUNB	12100
9	2505	39566584	CHANDAN	11-Dec-22			RAJAN PAL	6082	10-Dec-22	PUNB	18182
10	2751	39574859	RAJAN PAL	10-Dec-22			RAJAN PAL	11421	12-Dec-22	PUNB	29603
11	3411	39608264	CHANDAN	12-Dec-22			RAJAN PAL	3711	13-Dec-22	PUNB	33314
12	4805	39608111	CHANDAN	13-Dec-22
13	2700	39625463	CHANDAN	14-Dec-22
14	3600	39611283	CHANDAN	14-Dec-22
15	4225	39603022	RAJAN PAL	12-Dec-22
16	2902	39603295	RAJAN PAL	12-Dec-22
17	4294	39603153	RAJAN PAL	12-Dec-22
18	3900	39685842	CHANDAN	not paid
19	3711	39671011	RAJAN PAL	13-Dec-22
Sheet3

Cell Formulas
Range	Formula
M3:M11	M3	=SUMIF(I$3:I3,I3,J$3:J3)
F3:F19	F3	=IFERROR(AGGREGATE(15,6,K$3:K$11/((I$3:I$11=E3)*(M$3:M$11>=SUMIF(E$3:E3,E3,C$3:C3))),1),"not paid")

Thankyou sir its working. Sir could you pls explain how this formula is working and after applying the formula my excel sheet is being freezing and taking longer than usual in loading threads could you pls suggest how can i fix that.

 

[Peter_SSs]

Thankyou sir its working.

Good news! You're welcome.

Sir could you pls explain how this formula is working

The extra column keeps a record of the total amount paid by a person at a particular date. for example, by 12-Dec-22 CHANDAN has paid a total of 15,250

The last part of the column F formula sums the total invoices for that person up to the row the formula is on. For example, for cell F11 the total invoices for CHANDAN up to that point is 12,660 (sum of C7,C8,C9 & C11). The AGGREGATE part of the function then finds the earliest date in column K for which the column M value >= 12,660 and column I is CHANDRAN. That happens in row 5 so 12-Dec-22 is returned in F11

could you pls suggest how can i fix that

I have no worksheet formula fix for that. The formula has to calculate what it has to calculate, and the time that takes will depend on your machine and the amount of data.

Perhaps you could have a macro solution that calculates and places the fixed values in the cells rather than having formulas there.