Count number in cell which have both comma and dash in one cell

Please, try the next function. It builds a virtual discontinuous range and count its cells:

Function countNumb(strNo As String) As Long
    Dim arr, i As Long
    
    arr = Split(Replace(Replace(strNo, " ", ""), "-", ":"), ",")
    For i = 0 To UBound(arr)
        If Not InStr(arr(i), ":") > 0 Then
            arr(i) = arr(i) & ":" & arr(i)
        End If
    Next
    countNumb = Intersect(Range(Join(arr, ",")), Range("A:A")).cells.count
    Debug.Print Range(Join(arr, ",")).Address 'only to visually see the built range before intersection address...
End Function

It can also process a string as "2, 4,5,7 - 9"...

It can be tested using the next code:

Sub testCountNumbers()
    Dim x As String: x = "2,4,5,7-9"
    Debug.Print countNumb(x)
End Sub

With VBA,

Function AddNumbers(rngTarget As Range) As Long
    Dim arrValues() As String
    Dim lngValue As Long
    Dim strValue As String
    Dim lngMinimum As Long
    Dim lngMaximum As Long

    arrValues = Split(rngTarget.Text, ",")
    
    For lngValue = LBound(arrValues) To UBound(arrValues)
    
        strValue = arrValues(lngValue)
                
        If InStr(strValue, "-") > 0 Then
        
            lngMinimum = CLng(Left(strValue, InStr(strValue, "-") - 1))
            
            lngMaximum = CLng(Replace(strValue, lngMinimum & "-", vbNullString))
        
            AddNumbers = AddNumbers + ((lngMaximum - lngMinimum) + 1)
        
        Else
        
            AddNumbers = AddNumbers + 1
        
        End If
    
    Next lngValue
        
End Function

Assuming column XFD in the active sheet is empty, and that no integer within the string will ever exceed 2^20:

=SUM(COUNTIF(INDIRECT("XFD"&SUBSTITUTE(TEXTSPLIT(A2,","),"-",":XFD")),""))

For those without TEXTSPLIT:

=SUM(COUNTIF(INDIRECT(SUBSTITUTE(FILTERXML("<a><b>XFD"&SUBSTITUTE(A2,",","</b><b>XFD")&"</b></a>","//b"),"-",":XFD")),""))