How to find the maximum count of cells in excel having values in a range continuously?

Question

I have a huge data collected daily on our device. The data has 3 columns. Col1 is Date Col2 is Time Col3 is Data Value

My task is to find how many time the value in Col3 falls between specific range and report the "maximum continuous occurrence" for that day.

So if the table is like below,

Date	Time	Value
1Jan23	5am	6
1Jan23	6am	7
1Jan23	7am	5
1Jan23	8am	0
1Jan23	9am	2
1Jan23	10am	7
1Jan23	11am	5
1Jan23	12pm	0
2Jan23	5am	0
2Jan23	6am	1
2Jan23	7am	6
2Jan23	8am	5
2Jan23	9am	6
2Jan23	10am	7
2Jan23	11am	5
2Jan23	12pm	0
3Jan23	5am	1
3Jan23	6am	6
3Jan23	7am	6
3Jan23	8am	1
3Jan23	9am	2
3Jan23	10am	5
3Jan23	11am	1
3Jan23	12pm	9

and if I want to count maximum how many times the value was between 4 & 8 in column "Value", the answer I should get as :

Date	Max Count for Day
1Jan23	3
2Jan23	5
3Jan23	2

This is just an example. I have data of daily values for almost a year and need a way to show just maximum continuous count of value within specific range for each date.

Answer 1

You can try the following using Excel formulas, assuming no Excel version constraints per the tags listed in the question.

=LET(A,A2:A25,C,C2:C25,ux,UNIQUE(A),min,F1,max,H1,
 out, BYROW(ux, LAMBDA(u,LET(f, FILTER(C, A=u),
  MAX(SCAN(0,f, LAMBDA(ac,x, IF(AND(x>=min, x<=max),ac+1,0))))))),HSTACK(ux,out))

Here is the output:

Iterate over all unique dates via BYROW and for each unique date(u), it filters Value items for a given date, then uses SCAN to count the number of continuous values within a given range and finds the maximum value (maximum number of continuous values for a given date). Finally, HSTACK generates the output in the desired format. The formula assumes the input data is sorted by date (as in the sample of the question), otherwise, it must be adjusted.

Answer 2

I'll preface this by saying there is probably a way to do this without VBA, especially in newer versions of Excel so you might be better off waiting for another answer.

Anyway, this UDF should work:

Function continuousCount(ByVal rng As Range, ByVal testedDate As String, ByVal min As Double, ByVal max As Double) As Long
    Dim mcc As Long, ccc As Long, val As Double, currentDate As String, dataArr() As Variant
    dataArr = rng
    For i = 1 To UBound(dataArr)
        val = dataArr(i, 3)
        currentDate = dataArr(i, 1)
        
        If currentDate = testedDate Then
            If val >= min And val <= max Then
                ccc = ccc + 1
            Else
                If ccc > mcc Then
                    mcc = ccc
                    ccc = 0
                End If
            End If
        End If
        

    Next i
    
    If ccc > mcc Then mcc = ccc
    
    continuousCount = mcc
End Function

You can use it in your worksheet like so (I named the table "data"):

I used a pivot table to get the list of dates and put the formula in the column next to it.

It is rather inelegant and I still believe there is a way to do this without VBA in newer versions of excel as you can do pretty much everything with built-in functions. I only have Excel 2010 so this is the best I can do.