Count consecutive number with largest value and sum this

[meohen1992]

Hi everyone,
I am trying to find consecutive numbers with largest value.
I have used the formula then did not worked exactly like I want:
=MAX(FREQUENCY(IF(A2:A26>0,ROW(A2:A26)),IF(A2:A26<=0,ROW(A2:A26))))

This formula return longest sequence of numbers, not consecutive numbers with largest value...
The result I want is : 6 and 3839

Thanks,
Huy

Book1
	A	B	C	D	E	F
1	Value	Consecutive positive	Sum this		Consecutive with largest number	Sum this
2	623	11	1,785		6	3,839
3	212				Result Formula I want
4	109
5	291
6	280
7	2,325
8	-1,575
9	632
10	1,287
11	-1,175
12	368
13	-285
14	-497
15	-9
16	429
17	173
18	90
19	18
20	57
21	10
22	63
23	444
24	410
25	69
26	22
Sheet1

Cell Formulas
Range	Formula
B2	B2	=MAX(FREQUENCY(IF(A2:A26>0,ROW(A2:A26)),IF(A2:A26<=0,ROW(A2:A26))))
C2	C2	=MAX((COUNTIF(OFFSET(A2:A26,ROW(A2:A26)-ROW(A2),0,B2),">=0")=B2)*SUBTOTAL(109,OFFSET(A2:A26,ROW(A2:A26)-ROW(A2),0,B2)))
E2	E2	=COUNT(A2:A7)
F2	F2	=SUM(A2:A7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2	Cell Value	>0	text	NO
F2	Cell Value	<0	text	NO
C2	Cell Value	>0	text	NO
C2	Cell Value	<0	text	NO
A2:A26	Cell Value	>0	text	NO
A2:A26	Cell Value	<0	text	NO

 

[Fluff]

Did you try the formula I suggested? It's non-volatile & therefore won't calculate as often.

 

[meohen1992]

Did you try the formula I suggested? It's non-volatile & therefore won't calculate as often.

Hi sir.
Thanks for your idea. it works and runs correctly.
However I cannot filter when use it, like Anonymous1378's formula.

 

[Fluff]

However I cannot filter when use it

You never said you needed it to work on filtered data.

 

[Eric W]

If you want all the groups listed (I removed the requirement for the empty row):

Book1
	A	B	C	D	E	F	G
1	Value	Consecutive positive	Sum this		Starting row	Group size	Sum
2	622.51	11	1785.18		2	6	3839.36
3	211.5				9	2	1918.56
4	109.3				16	11	1785.18
5	291.31				12	1	368.26
6	280				13	3	-790.69
7	2324.74				11	1	-1175
8	-1575				8	1	-1575
9	631.5
10	1287.06
11	-1175
12	368.26
13	-285
14	-496.69
15	-9
16	428.98
17	173.09
18	90.02
19	18
20	56.63
21	10.39
22	63.26
23	444.14
24	410.3
25	68.68
26	21.69
Sheet4

Cell Formulas
Range	Formula
E2:G8	E2	=LET(a,IF(IFERROR(SIGN(A1:A26),0)<>SIGN(A2:A27),ROW(A2:A27)),c,COUNT(a)-1,s,SEQUENCE(c),sa,SMALL(a,s),sb,SMALL(a,s+1),d,SUBTOTAL(9,OFFSET(A1,sa-1,0,sb-sa)),SORT(CHOOSE({1,2,3},sa,sb-sa,d),3,-1))
Dynamic array formulas.

 

[meohen1992]

You never said you needed it to work on filtered data.

Sorry about that
I have mentioned in previous Anonymous1378's reply.
Thank you so much for helping me find the answer.

 

[meohen1992]

If you want all the groups listed (I removed the requirement for the empty row):

Book1
	A	B	C	D	E	F	G
1	Value	Consecutive positive	Sum this		Starting row	Group size	Sum
2	622.51	11	1785.18		2	6	3839.36
3	211.5				9	2	1918.56
4	109.3				16	11	1785.18
5	291.31				12	1	368.26
6	280				13	3	-790.69
7	2324.74				11	1	-1175
8	-1575				8	1	-1575
9	631.5
10	1287.06
11	-1175
12	368.26
13	-285
14	-496.69
15	-9
16	428.98
17	173.09
18	90.02
19	18
20	56.63
21	10.39
22	63.26
23	444.14
24	410.3
25	68.68
26	21.69
Sheet4

Cell Formulas
Range	Formula
E2:G8	E2	=LET(a,IF(IFERROR(SIGN(A1:A26),0)<>SIGN(A2:A27),ROW(A2:A27)),c,COUNT(a)-1,s,SEQUENCE(c),sa,SMALL(a,s),sb,SMALL(a,s+1),d,SUBTOTAL(9,OFFSET(A1,sa-1,0,sb-sa)),SORT(CHOOSE({1,2,3},sa,sb-sa,d),3,-1))
Dynamic array formulas.

Thank you for re-editing and adding find out the location of results.
I will use this function to further analyze the given data.

 

[meohen1992]

I think I have found the answer.
Thank you so much everyone for helping me.