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.

enter image description here

2 Answers 2


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

    countif(A1:A, C3), 
            left( not( (A1:A <> C3) + (B1:B >= 0) ) ) 
    "(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:

  • Thank you very much, it works
    – Ddamu
    Jan 9 at 7:52
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.