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)))

 

[jasonb75]

Should the streak break and restart when it hits a hidden row or continue past it?

This will work if the streak should be broken, I haven't looked at one to continue yet as that will be more complex so I'll wait to see if it's needed first.

=IF(SIGN(A2)=SIGN(AGGREGATE(9,5,A1)),N(D1)+SIGN(A2),SIGN(A2))

 

[NeedExcelHelp2021]

Should the streak break and restart when it hits a hidden row or continue past it?

This will work if the streak should be broken, I haven't looked at one to continue yet as that will be more complex so I'll wait to see if it's needed first.

=IF(SIGN(A2)=SIGN(AGGREGATE(9,5,A1)),N(D1)+SIGN(A2),SIGN(A2))

Thank You For The Reply

I need it to continue when i hit hidden row

 

[Alex Blakenburg]

Try this:

=IF(SUBTOTAL(3,A2)=1,IFERROR(IF(SIGN(A2)=SIGN(F1),N(F1)+SIGN(A2),SIGN(A2)),SIGN(A2)),N(F1))

 

[NeedExcelHelp2021]

Try this:

=IF(SUBTOTAL(3,A2)=1,IFERROR(IF(SIGN(A2)=SIGN(F1),N(F1)+SIGN(A2),SIGN(A2)),SIGN(A2)),N(F1))

worked perfectly

jasonb75 Alex Blakenburg Thank You Much Appreciated.​

 

[Alex Blakenburg]

Thank you for letting us know. Glad we could help.

 

[NeedExcelHelp2021]

sorry to bother you again, but can you change the formula to work with sum instead of count, try changing subtotal 3 to 9 but don't work
If you could help that would be awesome

Book1
	A	B	C	D	E	F
1	W/L			W/L Streak	W/L Streak $
2	$ 579			1	$ 579
3	$ (354)			-1	$ (354)
4	$ 741			1	$ 741
5	$ 2,242			2	$ 2,242
6	$ 2,972			3	$ 5,214
7	$ 1,044			4	$ 6,258
8	$ 2,704			5	$ 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
18	$ 661			2	$ 1,042
19	$ 545			3	$ 1,587
20	$ 2,020			4	$ 3,607
21
Sheet1

Cell Formulas
Range	Formula
D2:D20	D2	=IF(SUBTOTAL(3,A2)=1,IFERROR(IF(SIGN(A2)=SIGN(D1),N(D1)+SIGN(A2),SIGN(A2)),SIGN(A2)),N(D1))

 

[jasonb75]

Try this in E2 and fill down

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

 

[NeedExcelHelp2021]

Try this in E2 and fill down

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

your formula works but not when i filter Cell A, can you change it to work on filtered cells

 

[jasonb75]

It works for me with filtered rows, I would need to see an example where the result is incorrect to figure out the reason why.