HI

I need to count the values between a set value, and then only show how many times this count was more than 5. The values counted is variable only the between value is set

This is in the same column lets say from a1. DD is your set value, in the below example my answer should be 2 ( there are only 2occurrences where there are more than 5 data sets between the DD parameter)



1
5
4
7
DD
7
5
2
9
7
1
5
3
1
1
2
DD
6
8
7
1
DD
7
5
2
9
7
1
5
3
1
1
2
DD
6
8
7


Thank you
Gerhard

Hi,

Am Fri, 3 Jun 2016 06:26:27 -0700 (PDT) schrieb :

I need to count the values between a set value, and then only show how many times this count was more than 5. The values counted is variable only the between value is set

This is in the same column lets say from a1. DD is your set value, in the below example my answer should be 2 ( there are only 2occurrences where there are more than 5 data sets between the DD parameter)

try it with an UDF:

Function CntSeries(myRng As Range) As Long
Dim First As Long, i As Long

First = 1
For i = 1 To myRng.Rows.Count
If Cells(i, myRng.Column) = "DD" And i - First = 6 Then
CntSeries = CntSeries + 1
First = i
End If
Next
End Function


Regards
Claus B.
--
Windows10
Office 2016

Hi again,

Am Fri, 3 Jun 2016 17:12:17 +0200 schrieb Claus Busch:

try it with an UDF:

Function CntSeries(myRng As Range) As Long

sorry, there is an error in the function.
Better try:

Function CntS(myRng As Range) As Long
Dim varMatch() As Variant
Dim myCnt As Long, i As Long
Dim myRows As String

myRows = "1:" & myRng.Rows.Count
myCnt = Application.CountIf(myRng, "DD")
ReDim varMatch(myCnt - 1)
For i = 1 To myCnt
With Application
varMatch(i - 1) = _
Application.Evaluate("=small(if(" & myRng.Address & _
"=""DD"",row(" & myRows & "))," & i & ")")
End With
Next
For i = LBound(varMatch) To UBound(varMatch) - 1
If varMatch(i + 1) - varMatch(i) - 1 5 Then
CntS = CntS + 1
End If
Next
If myRng.Rows.Count - varMatch(UBound(varMatch)) - 1 5 Then CntS =
CntS + 1
End Function


Regards
Claus B.
--
Windows10
Office 2016

Hi,

Am Fri, 3 Jun 2016 06:26:27 -0700 (PDT) schrieb :

I need to count the values between a set value, and then only show how many times this count was more than 5. The values counted is variable only the between value is set

This is in the same column lets say from a1. DD is your set value, in the below example my answer should be 2 ( there are only 2occurrences where there are more than 5 data sets between the DD parameter)

you also can do it with a helper column.
Write in B2:
=IF(A2="DD",COUNT($A$1:A2)-SUM(B$1:B1),"")
and copy down.
Then you can count the series 5 with:
=COUNTIF(B:B,"5")


Regards
Claus B.
--
Windows10
Office 2016