Return Sum values only when the sum of them is the MAX sum based on another column

Question

I'm either looking for a formula solution or vba code to calculate the data on cells L2 and M2.

Explanation

I've a table from column range G:I, the table is registering the days from 1 to 1000, the value is the amount sold ( it can be a credit or debit) , when is a Credit, column "I" will display "Good Day", otherwise "Bad day" for debits. All of this calculation is being processed correctly, the problem comes when I want to get the max sum value for either the consecutive "Good Days" or "Bad Days"

Day 1 and Day 2 are consecutive "Bad Days" and the sum of both values is 200 ( so far this is the maximum sum value ), the problem comes when I get again consecutive "Bad Days" and the sum of those values is greater than my initial consecutive max sum values. Day 500 and 501 are consecutive "Bad Days" and the sum of both is 600 ( that is what I want in cell L2). The same process for "Good Day"

Consecutive days mean more than 1 day either being "Bad Day" or "Good Day" , example, 2 days,4 days, 100 days,etc. Let's say I've got 100 consecutive "Bad Days" with a sum of 1000 , then I got 1 "God Day" and then I got 2 consecutive "Bad Days" with a sum of 5000 which is greater than the one I've got in the 100 consecutive "Bad days" before ( I will need the 5000 value on cell L2)

On the picture below, the first 2 days are consecutive "Bad days" and the total sum is 200 ( so far this is the max value). From day 4 to day 10 there 7 consecutive "Bad Days" and the total sum is 35 ( which is less than the total of previous consecutive values which was 200). Now Days 500 and 501 are 2 consecutive "Bad Days" and the total sum is 600 which is greater than the previous 2 consecutive days on day 1 and 2. 600 will be now my max value that I need on cell L2 ( if someone change the results or values, L2 must be updated too

Answer 1

I've figured out how to do it by building a vba code to sum only the consecutive data groups when consecutive data on each cell is equal or greater than 2 but also if column on the left side of the cell has "POSITIVE" or "NEGATIVE" value ( if condition was not meet leave cell empty and go to next cell dow)

'Calcular Suma de Stops Seguidos
    Dim IteracionesFinales As String
    IteracionesFinales = (Fil_F - Fil_I)
    
    'Rango descendente
    Dim r As String
    r = 9
    'Restar Filas
    Dim rf As Integer
    'Maximo de itraciones
    
    For i = 1 To IteracionesFinales
    
    If Range("AA" & r).Value = "" Then
    r = r + 1
    
    Else:
    
    rf = (Range("AA" & r).Value - 1) * -1
    
    Range("AB" & r).Select
    ActiveCell.FormulaR1C1 = _
    "=IF(AND(RC[-1]>=2,RC[-2]=""NEGATIVA""),SUM(R[" & rf & "]C[-4]:OFFSET(RC[-4],0,0)),"""")"
    
    r = r + 1
    rf = 0
    
    End If
    
    
    Next i
        
    
     'Calcular Suma de Takes Seguidos
 
    'Rango descendente
    Dim r2 As String
    r2 = 9
    'Restar Filas
    Dim rf2 As Integer
    'Maximo de itraciones
    
    For i = 1 To IteracionesFinales
    
    If Range("AA" & r2).Value = "" Then
    r2 = r2 + 1
    
    Else:
    
    rf2 = (Range("AA" & r2).Value - 1) * -1
    
    Range("AC" & r2).Select
    ActiveCell.FormulaR1C1 = _
    "=IF(AND(RC[-2]>=2,RC[-3]=""POSITIVA""),SUM(R[" & rf2 & "]C[-4]:OFFSET(RC[-4],0,0)),"""")"
    
    r2 = r2 + 1
    rf2 = 0
    
    End If
    
    
    Next i
    
    Range("Q19").Select
    ActiveCell.FormulaR1C1 = _
        "=MAXIFS(R[-11]C[11]:R[370]C[11],R[-11]C[9]:R[370]C[9],""NEGATIVA"")"
    Range("R19").Select
    ActiveCell.FormulaR1C1 = _
        "=MAXIFS(R[-11]C[11]:R[370]C[11],R[-11]C[8]:R[370]C[8],""POSITIVA"")"

The above code is putting the sum data by each group in the last 2 columns AB,AC . Then I just needed a MAXIFS function to get the max sum amount

Answer 2

In cell K3 use

=MAX(IF($I$2:$I$14=K2,$H2:$H14,0)+IF($I$3:$I$15=K2,$H3:$H15,0))

If you don't have Office 365, you need to confirm the formula with Ctrl+Shift+Enter.

Copy across to cell L3.