Past dates Cell Reference

[Muthukrishnan V]

Past dates.xlsx
	A	B	C	D	E	F	G	H
1		Helper:>>	12-06-2024
2						Dessired Output
3		general	dd-mm-yyyy	numerical		Past Dates
4		Holder	Date	Amount		Holder	Cell Ref
5		KM	03-02-2024	15000		KM	F5
6		KM	11-06-2024	10000		KM	F6
7		KM	17-07-2024	5000		VM	F11
8				30000		VM	F12
9						VB	F19
10
11		VM	04-04-2024	8000
12		VM	19-05-2024	7000
13		VM	21-10-2024	5000
14		VM	23-10-2024	10000
15
16				30000
17
18
19		VB	04-04-2024	1000
20		VB	17-06-2024	10000
21		VB	21-07-2024	18000
22		VB	02-01-2025	5000
23
24				34000
25
Sheet1

Cell Formulas
Range	Formula
C1	C1	=TODAY()
D8	D8	=SUM(D5:D7)
D16,D24	D16	=SUM(D11:D14)

Past date is derived based on C1.
There are blank rows in between holders.
Request formula for output in Columns F and G.
Thanking you sirs

 

[Muthukrishnan V]

I am sorry I have made a mistake. In Column G, I have mentioned Cell references as F5,F6,F11,F12 and F19 by mistake. They should be D5,D6,D11,D12 and D19. Kindly pardon me for my lapses. Thanking you sirs,

 

[Eric W]

Try:

Book2
	A	B	C	D	E	F	G
1		Helper:>>	6/12/2024
2						Dessired Output
3		general	dd-mm-yyyy	numerical		Past Dates
4		Holder	Date	Amount		Holder	Cell Ref
5		KM	2/3/2024	15000		KM	D5
6		KM	6/11/2024	10000		KM	D6
7		KM	7/17/2024	5000		VM	D11
8				30000		VM	D12
9						VB	D19
10
11		VM	4/4/2024	8000
12		VM	5/19/2024	7000
13		VM	10/21/2024	5000
14		VM	10/23/2024	10000
15
16				30000
17
18
19		VB	4/4/2024	1000
20		VB	6/17/2024	10000
21		VB	7/21/2024	18000
22		VB	1/2/2025	5000
23
24				34000
Sheet4

Cell Formulas
Range	Formula
F5:G9	F5	=LET(holder,B5:B100,date,C5:C100,amt,D5:D100,t,CHOOSE({1,2},holder,ADDRESS(ROW(amt),COLUMN(amt),4)),FILTER(t,(date<C1)*(date<>"")))
D8	D8	=SUM(D5:D7)
D16,D24	D16	=SUM(D11:D14)
Dynamic array formulas.

 

[Cubist]

Another option.

Book1
	A	B	C	D	E	F	G
1		Helper:>>	6/12/24
2						Dessired Output
3		general	dd-mm-yyyy	numerical		Past Dates
4		Holder	Date	Amount		Holder	Cell Ref
5		KM	2/3/24	15000		KM	D5
6		KM	6/11/24	10000		KM	D6
7		KM	7/17/24	5000		VM	D11
8				30000		VM	D12
9						VB	D19
10
11		VM	4/4/24	8000
12		VM	5/19/24	7000
13		VM	10/21/24	5000
14		VM	10/23/24	10000
15
16				30000
17
18
19		VB	4/4/24	1000
20		VB	6/17/24	10000
21		VB	7/21/24	18000
22		VB	1/2/25	5000
23
24				34000
Sheet4

Cell Formulas
Range	Formula
F5:G9	F5	=WRAPROWS(TOCOL(IFS((C5:C24<C1)*(B5:B24<>""),HSTACK(B5:B24,CHAR(COLUMN(D5:D24)+64)&ROW(D5:D24))),2),2)
D8	D8	=SUM(D5:D7)
D16,D24	D16	=SUM(D11:D14)
Dynamic array formulas.

 

[Muthukrishnan V]

Thank you Mr Eric. My problem is solved. I am grateful to the Forum.

 

[Cubist]

One more with FILTER.

Book1
	A	B	C	D	E	F	G
1		Helper:>>	6/12/24
2						Dessired Output
3		general	dd-mm-yyyy	numerical		Past Dates
4		Holder	Date	Amount		Holder	Cell Ref
5		KM	2/3/24	15000		KM	D5
6		KM	6/11/24	10000		KM	D6
7		KM	7/17/24	5000		VM	D11
8				30000		VM	D12
9						VB	D19
10
11		VM	4/4/24	8000
12		VM	5/19/24	7000
13		VM	10/21/24	5000
14		VM	10/23/24	10000
15
16				30000
17
18
19		VB	4/4/24	1000
20		VB	6/17/24	10000
21		VB	7/21/24	18000
22		VB	1/2/25	5000
23
24				34000
Sheet4

Cell Formulas
Range	Formula
F5:G9	F5	=FILTER(HSTACK(B5:B24,CHAR(COLUMN(D5:D24)+64)&ROW(D5:D24)),(C5:C24<C1)*(B5:B24<>""))
D8	D8	=SUM(D5:D7)
D16,D24	D16	=SUM(D11:D14)
Dynamic array formulas.

 

[Muthukrishnan V]

I want a little more help. I want the FILTER to give output only if D5:24 is not blank or not 0.
Kindly advise me amended formula. Thanking you

 

[Cubist]

You can try

=FILTER(HSTACK(B5:B24,CHAR(COLUMN(D5:D24)+64)&ROW(D5:D24)),(D5:D24>0)*(C5:C24<C1)*(B5:B24<>""))

 

[Muthukrishnan V]

Thank you Mr Cubist sir. Works great. I am grateful to the forum.