With
A2:A10 containing groups of positive an negative numbers.
Example:
-2
1
4
-3
0
5
9
8
-7
This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, (instead of just
ENTER) returns the max count of consecutive postive values:
B1: =MAX(FREQUENCY(IF(A2:A100>0,ROW(A2:A100)),IF(A2:A100<=0,ROW(A2:A100))))
And this ARRAY FORMULA returns the sum of items in that group:
C1: =SUM(OFFSET(A1,SMALL(IF(A2:A100<=0,ROW(A2:A100)),MATCH(B1,FREQUENCY(
IF(A2:A100>0,ROW(A2:A100)),IF(A2:A100<=0,ROW(A2:A100))),0))-B1-1,,B1))
In the above example, the Max Group is: 3 (the 5,9,8 group)
and the sum of that group is: 22
Adjust range references to suit your situation.
Is that something you can work with?
Bookmarks