counting a streak of values

[jimblimm]

i have this formula
=COUNTIFS(B1:B10,"s",B2:B11,"s",B3:B12,"s",B4:B13,"s",B5:B14,"s")

i am wanting to count if the s streaks 4 times....
example

s
s
s
s

is there a shorter formula i can use to do the exact same thing but i can have a variable number of times it should streak in a different cell
example
b1=number of time the "s" repeat/streaks

 

[Robert Mika]

Try this:

Sub Count()
Dim tbl(), s As String, i&

tbl = Application.Transpose(Range("b1:b20"))
s = Join(tbl, "")

For i = 1 To Len(s) - 4
  If Mid(s, i, 4) = "SSSS" Then
    x = x + 1
    ReDim Preserve tbl(1 To x)
    tbl(x) = i + 4
  End If
Next i
Cells(1, 3).Value = UBound(tbl)
End Sub