0 votes

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 277 views

## 1 answer to this question.

0 votes

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))`
answered Mar 17, 2023 by
• 38,010 points

0 votes
1 answer

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

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

0 votes
2 answers

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

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

0 votes
1 answer

## How to get current time and date in Android

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

0 votes
1 answer

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

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

0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

0 votes
1 answer

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

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

0 votes
0 answers

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

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

0 votes
1 answer

## IF - ELSE IF - ELSE Structure in Excel

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

0 votes
1 answer

## How to calculate median of unsorted values

In vba code: Dim r As Range Dim result 'Set ...READ MORE

0 votes
1 answer

## VBA how to calculate depth of items in excel, simliar to a BOM system

Add on the sheet an ActiveX Microsoft ...READ MORE