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).

Feb 26, 2023 in Others 307 views

## 1 answer to this question.

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))
• 38,010 points

## How to take a screenshot of a current Activity and then share it?

For me, I captured and then shared ...READ MORE

## How to get the URL of the current tab in Google Chrome?

Its so simple.... If you want to ...READ MORE

## How to get current time and date in Android

In order to get the current date ...READ MORE

## How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE