Remember Me?

#1
June 3rd 16, 02:26 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2016 Posts: 1
Countifs between a certain value

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

#2
June 3rd 16, 04:12 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,853
Countifs between a certain value

#3
June 3rd 16, 07:43 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,853
Countifs between a certain value

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) = _
"=""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
#4
June 3rd 16, 09:29 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,853
Countifs between a certain value

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Malcolm Austin Excel Worksheet Functions 1 March 17th 14 09:22 PM JP Ronse Excel Worksheet Functions 5 April 9th 10 03:29 AM Steve Excel Worksheet Functions 2 January 4th 09 05:36 PM Russell Excel Discussion (Misc queries) 2 August 19th 08 02:18 AM NewToVB Excel Programming 0 February 13th 08 09:26 PM

All times are GMT +1. The time now is 12:33 PM.