Sub uuu()
    Dim sFolder$, sFile$
    Dim s#
    Dim j As Byte
    Dim sd As Object
    Dim el, sp
'-------------------
   'выбираем папку
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Выберите папку"
        .AllowMultiSelect = False
        .InitialFileName = ThisWorkbook.Path
        If .Show <> -1 Then Exit Sub
        sFolder = .SelectedItems(1)
    End With
    
    'получаем размер папки
    With CreateObject("Scripting.FileSystemObject")
        s = .GetFolder(sFolder).Size
    End With
    
    'считаем файлы
    Set sd = CreateObject("Scripting.Dictionary")
    sFile = Dir(sFolder & "\*.*")
    Do While sFile <> ""
        sp = Split(sFile, ".")
        sd.Item(sp(UBound(sp))) = sd.Item(sp(UBound(sp))) + 1
        sFile = Dir
    Loop
    
    'выгружаем данные на лист
    For Each el In sd.Keys
        j = j + 1
        Cells(2, j) = el
        Cells(3, j) = sd.Item(el)
    Next
    Cells(2, j + 1) = "Размер папки"
    Cells(3, j + 1) = s
End Sub