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

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.

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) & "')"
)
)
``````

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"))))
``````
• Thank you very much, it works Jan 9 at 7:52
• I think this gives the total number of rows that have a negative value on the date, rather than the max number of consecutive rows that meet the criteria. The results will be correct when the dates in A1:A are sorted and there are no positive values in between negative values, but they will be incorrect when the dates are in mixed order and also when there are positive values in between negative values on a date. Jan 9 at 9:11