안녕하세요~

아래 첨부파일로 적용해보세요.

Public Sub 그룹별숫자카운트()
    On Error GoTo EH

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Application.ScreenUpdating = False

    Dim tgt As Long
    Dim sIn As String
    sIn = InputBox("첫번째 괄호 '(' 앞의 숫자를 입력하세요.", "그룹별 카운트")
    If Len(Trim$(sIn)) = 0 Then Exit Sub
    If Not IsNumeric(sIn) Then Exit Sub
    tgt = CLng(sIn)

    Dim sCol As Long, eCol As Long
    sCol = ws.Range("I1").Column
    eCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    If eCol < sCol Then Exit Sub

    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")

    Dim c As Long, lr As Long
    Dim grp As String

    Dim arr As Variant
    Dim i As Long
    Dim s As String
    Dim n As Long
    Dim cnt As Long
    Dim last As String

    For c = sCol To eCol
        grp = Trim$(CStr(ws.Cells(1, c).Value))
        If Len(grp) = 0 Then GoTo NextCol

        lr = ws.Cells(ws.Rows.Count, c).End(xlUp).Row
        If lr < 2 Then
            d(grp) = Array(tgt, 0&, vbNullString)
            GoTo NextCol
        End If

        arr = ws.Range(ws.Cells(2, c), ws.Cells(lr, c)).Value2

        cnt = 0
        last = vbNullString

        If IsArray(arr) Then
            For i = 1 To UBound(arr, 1)
                If Len(CStr(arr(i, 1))) > 0 Then
                    s = CStr(arr(i, 1))
                    n = LeadNum(s)
                    If n = tgt Then
                        cnt = cnt + 1
                        last = s
                    End If
                End If
            Next i
        Else
            '//' 단일 셀 범위인 경우(방어)
            s = CStr(arr)
            n = LeadNum(s)
            If n = tgt Then
                cnt = 1
                last = s
            End If
        End If

        d(grp) = Array(tgt, cnt, last)

NextCol:
    Next c

    '//' 출력
    Dim k As Variant
    Dim out() As Variant
    Dim r As Long, m As Long

    m = d.Count
    If m = 0 Then Exit Sub

    ReDim out(1 To m, 1 To 4)
    r = 0

    For Each k In d.Keys
        r = r + 1
        out(r, 1) = CStr(k)                 '//' A: 그룹명
        out(r, 2) = d(k)(0)                 '//' B: 입력 숫자
        out(r, 3) = d(k)(1)                 '//' C: 갯수
        out(r, 4) = d(k)(2)                 '//' D: 마지막 매칭 셀값
    Next k

    With ws.Range("A1:D1")
        .Value = Array("그룹명", "입력숫자", "갯수", "마지막갯수 셀값")
        
        '// 헤더 서식
        .Interior.Color = RGB(0, 0, 0)        '// Black 배경
        .Font.Color = RGB(255, 255, 255)      '// White 글씨
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    
        '// 가는 선 테두리
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
    End With

    '// 열 너비 자동 맞춤
    ws.Columns("A:D").AutoFit

    ws.Range("A2").Resize(m, 4).Value = out
    
    Application.ScreenUpdating = True

    Exit Sub

EH:
    MsgBox "오류 " & Err.Number & vbCrLf & Err.Description, vbCritical
End Sub

Private Function LeadNum(ByVal s As String) As Long
    '//' 첫 번째 "(" 앞의 숫자 추출. 실패 시 -1
    Dim p As Long
    Dim t As String

    s = Trim$(s)
    If Len(s) = 0 Then
        LeadNum = -1
        Exit Function
    End If

    p = InStr(1, s, "(", vbTextCompare)
    If p <= 1 Then
        LeadNum = -1
        Exit Function
    End If

    t = Trim$(Left$(s, p - 1))
    If Len(t) = 0 Then
        LeadNum = -1
    ElseIf IsNumeric(t) Then
        LeadNum = CLng(t)
    Else
        LeadNum = -1
    End If
End Function

​