Find Largest consecutive occurrence of negative values

Hi, all

I've been struggling around this one, might be that my brain is just fried at this point. I already have a formula to help me find the biggest range of consecutive negative values. What I'm looking for now is a formula to find the largest consecutive negative sum value no matter if its got a smaller frequency .

Let me give you an example to make things a bit clearer, as I might not be explaining myself correctly:

From the following numbers:
1400
-400
640
125
222
2345
-2345
-235
234
3456
-1
-123
-1324


I want to be able to spot the section with the max negative value of -2580 ( -2345 + -235 ) .

I'm using the following formula to identify the largest range of negative values (-1,-123,-1324) :

{=MAX(FREQUENCY(IF(H7:H55<0, ROW(H7:H55)), IF(H7:H55>=0, ROW(H7:H55))))} which equals 3 (this is located in Q28)

& the following to get it to sum that section for me:

{=SUM(OFFSET(H7,MATCH(Q28,COUNTIF(OFFSET(H7,ROW(INDIRECT("1:"&1+ROWS(H7:H55)-Q28))-1,0,Q28,1),"<0"),0)-1,0,Q28,1))} which then amounts to -1448


Thus, even if the frequency is less than the max and the total sum of that frequency is the biggest, I want it to list that number or sum value. So even though the set of 2 consecutive negatives ( -2345, -235 ) are less than the 3 consecutive negatives (-1,-123,-1324) in frequency, it's got the highest negative value between the 2.

The numbers run in a column downwards from H7:H55 . If you want to see the spreadsheet, just let me know.

Thanks in advance!

Thanks XOR,

Solved the issue!

I have actually. Where would it hit problems?

Ok, I just saw what you meant...