How to find the maximum number of consecutive negative occurrences in GOOGLE SHEET using the specified cells?

Question

I can now find the maximum number of consecutive negative occurrences of B:B.

However, my problem is that I want to find the maximum number of consecutive occurrences of negative B:B that match the C3 date by specifying the C3 date and finding B:B and A:A.

Answer 1

Put multiple conditions in the Boolean expression inside the left(), like this:

=arrayformula( 
  if( 
    countif(A1:A, C3), 
    max( 
      len( 
        split( 
          join( 
            "", 
            left( not( (A1:A <> C3) + (B1:B >= 0) ) ) 
          ), 
          "F" 
        ) 
      ) 
    ), 
    "(no data for date '" & to_text(C3) & "')" 
  ) 
)

Answer 2

We will want to first sort the data, then do the operation. This is simple enough. Just change B1:B20 to: =FILTER(B:B, A:A = C3)

so your final formula will look something like this:

=ARRAYFORMULA(MAX(LEN(SPLIT(JOIN( "", LEFT(FILTER(B:b, A:A = C3))), "F"))))