How to calculate current streak of conditional number and excluding blanks

Question

Please be patient with me if I'm not too familiar with things around here because this is my first post on StackOverflow. Here's the problem:

Number of connections
 1777
 2500
 4227
  355
11332
 8402
 1972
 2285
 1828
 2249
 1138
 4082
 4121
 1969
 4289
 2728
12000
13000
15000
13000
13250

I write this piece every single day. When there are more than 10,000 connections, I want to be able to retrieve the current streak, and I want this number to be updated each time a new connection is entered.
As an illustration, the streak would be 5 today (12000, 13000, 15000, 13000, and 13250), however if I entered 3200 the following day, I would like the number to return to 0.

I experimented with several fixes for this problem. A resource column I created is titled "CONNECTIONS TO HIDE with."

=IFS(
   [@[Number of connections]]=""; "";
   [@[Number of connections]]>10000; "Win";
   [@[Number of connections]]<10000; "Loss"
)

And then in the streak cell, the formula:

=COUNTA(Table8[CONNECTIONS TO HIDE]) -
 MATCH(2;INDEX(1/(Table8[CONNECTIONS TO HIDE]="Loss");0))

It did not work.

I tried a simpler approach with:

=COUNTA(Table8[Number of connections]) -
 MATCH(2;INDEX(1/(Table8[Number of connections]<10000);0))

but still without result.

The main problem here seems to be that I can't make the formula ignore empty cells that will be filled in later. In fact, it works just fine when I merely drag my selection to the values I've previously entered rather than the entire column (including blanks).

Answer 1

To solve this  use xmatch if it's available in your version of Excel to do a reverse search:

=ROWS(Table1)-XMATCH(TRUE,Table1[Number of connections]<10000,0,-1)

To ignore blanks:

=LET(data,Table1[Number of connections],filter,FILTER(data,data<>""),COUNT(filter)-XMATCH(TRUE,filter<10000,0,-1))