Can Someone Change This Formula To Work On Filter Cells

[NeedExcelHelp2021]

Hello Everyone Hope Your Doing Well.

I Found This Formula Online (Running W/L Streak) But It Don't Work On Filtered Cells,
Can Someone Change it To Work On Filtered Cells

Thank You Much Appreciated.

Book1
	A	B	C	D	E
1	W/L			W/L Streak
2	579			1
3	(354)			-1
4	741			1
5	2,242			2
6	2,972			3
7	1,044			4
8	2,704			5
9	(1,852)			-1
10	(2,308)			-2
11	266			1
12	(404)			-1
13	577			1
14	1,561			2
15	1,053			3
16	(242)			-1
17	497			1
18	661			2
19	545			3
20	2,020			4
21
Sheet1

Cell Formulas
Range	Formula
D2:D20	D2	=IF(A2="",0,IF(ISNUMBER(INDIRECT(ADDRESS(ROW(A2)-1,4,4,1))),IF(INDIRECT(ADDRESS(ROW(A2)-1,4,4,1))>0,IF(A2>0,INDIRECT(ADDRESS(ROW(A2)-1,4,4,1))+1,-1),IF(A2<0,INDIRECT(ADDRESS(ROW(A2)-1,4,4,1))-1,1)),IF(A2>0,1,-1)))

 

[NeedExcelHelp2021]

when i filter out Cell A5 and Cell A18 the result is incorrect
cell E8 result should be 7460 and cell E20 result should be 3062

Book2
	A	B	C	D	E
1	W/L			W/L Streak	W/L Streak $
2	$ 579			1	$ 579
3	$ (353)			-1	$ (353)
4	$ 740			1	$ 740
6	$ 2,972			2	$ 5,214
7	$ 1,044			3	$ 6,258
8	$ 2,704			4	$ 8,962
9	$ (1,852)			-1	$ (1,852)
10	$ (2,308)			-2	$ (4,160)
11	$ 266			1	$ 266
12	$ (404)			-1	$ (404)
13	$ 577			1	$ 577
14	$ 1,561			2	$ 2,138
15	$ 1,053			3	$ 3,191
16	$ (242)			-1	$ (242)
17	$ 497			1	$ 497
19	$ 545			2	$ 1,205
20	$ 2,020			3	$ 3,225
21
Sheet1

Cell Formulas
Range	Formula
D2:D4,D6:D17,D19:D20	D2	=IF(SUBTOTAL(3,A2)=1,IFERROR(IF(SIGN(A2)=SIGN(D1),N(D1)+SIGN(A2),SIGN(A2)),SIGN(A2)),N(D1))
E2:E4,E6:E17,E19:E20	E2	=IF(ABS(D2)=1,A2,SUBTOTAL(9,A2)+N(E1))

 

[jasonb75]

Alex's formula wasn't doing quite what I thought, this looks like it works this time although I have only done a quick test.

=IF(ABS(AGGREGATE(9,5,D2))=1,A2,SUBTOTAL(9,A2)+N(E1))

 

[NeedExcelHelp2021]

Alex's formula wasn't doing quite what I thought, this looks like it works this time although I have only done a quick test.

=IF(ABS(AGGREGATE(9,5,D2))=1,A2,SUBTOTAL(9,A2)+N(E1))

works perfectly
Thank you So Much For Your hard Work Much Appreciated.