Option Explicit
    
Function CountStringCombi(s1 As String, s2 As String, v As Variant) As Variant
'Returns array with indices <i> showing how often
's1 is followed by <i> instances of s2 in v.
'Example: If v includes
'ABC
'DEF
'GHI
'GHI
'ABC
'DEF
'ABC
'DEF
'then CountStringCombi("ABC","DEF",v) will return {3} and
'CountStringCombi("DEF","GHI",v) will return {0;1}. Please note that this
'function has to be array-entered (enter with CTRL + SHIFT + ENTER).
'Source (EN): https://www.sulprobil.com/count_string_combinations_en/
'Source (DE): https://www.bplumhoff.de/count_string_combinations_de/
'(C) (P) by Bernd Plumhoff 10-Aug-2009 PB V0.2
Dim i As Long, hit As Long, maxhit As Long, blFound As Boolean, vP As Variant

With Application.WorksheetFunction
maxhit = 1
vP = .Transpose(.Transpose(v)) 'Range or array - make it same
ReDim vR(1 To UBound(vP)) As Variant
For i = 1 To UBound(vP)
    Select Case vP(i, 1)
    Case s1
        blFound = True
    Case s2
        If blFound Then
            hit = hit + 1
            GoTo nexti
        End If
    Case Else
        blFound = False
    End Select
    If hit > 0 Then
        vR(hit) = vR(hit) + 1
        If hit > maxhit Then maxhit = hit
        hit = 0
    End If
nexti:
Next i
If hit > 0 Then
    vR(hit) = vR(hit) + 1
    If hit > maxhit Then maxhit = hit
    hit = 0
End If
ReDim Preserve vR(1 To maxhit) As Variant
CountStringCombi = .Transpose(vR)
End With
End Function