I don't know why a formula couldn't work. In C9 use,

=SUMPRODUCT(--LEFT(B5:G5&0))
'an array formula that will skip over blank and text cells
=SUM(IFERROR(--LEFT(B5:G5&0), 0))

Sum of First Digits

The Function

Function SFD(Range As Range) As Long
    Dim vnt As Variant, i As Long, j As Long
    vnt = Range
    For i = 1 To UBound(vnt)
        For j = 1 To UBound(vnt, 2)
            If IsNumeric(vnt(i, j)) Then
                If CLng(vnt(i, j)) > 0 Then SFD = SFD + CLng(Left(vnt(i, j), 1))
            End If
        Next
    Next
End Function

In cell C9 use the formula: =SFD(B5:G5).

Array Version

Sub SumOfFirstDigit()

    Const cRow As Long = 5
    Const cCol1 As Variant = 2
    Const cCol2 As Variant = 7
    Const cTarget As String = "C9"

    Dim j As Long
    Dim sumFirst As Long
    Dim vnt As Variant

    vnt = Range(Cells(cRow, cCol1), Cells(cRow, cCol2))

    For j = 1 To UBound(vnt, 2)
        If IsNumeric(vnt(1, j)) Then
            If CLng(vnt(1, j)) > 0 Then _
                    sumFirst = sumFirst + CLng(Left(vnt(1, j), 1))
        End If
    Next

    Range(cTarget).Value = sumFirst

End Sub

Range Version

Sub SumOfFirstDigit2()

    Const cRow As Long = 5
    Const cCol1 As Variant = 2
    Const cCol2 As Variant = 7
    Const cTarget As String = "C9"

    Dim j As Long
    Dim sumFirst As Long

    For j = cCol1 To cCol2
        If IsNumeric(Cells(cRow, j)) Then
            If Cells(cRow, j) > 0 Then _
                    sumFirst = sumFirst + CLng(Left(Cells(cRow, j), 1))
        End If
    Next

    Range(cTarget).Value = sumFirst

End Sub

Quick Repair

• You forgot Dim cellref As Range.
• You forgot Set in Set cellref = Cells(5, colnum).
• You misspelled celref in sumfirst = sumfirst + (Left(cellref, 1)).
• You forgot Set cellref = Nothing
• Integer is dead, Long prevails.

The Code

Sub sum_first_digit()

    Dim cellref As Range
    Dim colnum As Long
    Dim sumfirst As Long

    sumfirst = 0

    For colnum = 2 To 7
        Set cellref = Cells(5, colnum)
        sumfirst = sumfirst + (Left(cellref, 1))
    Next colnum

    Range("C9").Value = sumfirst

    Set cellref = Nothing

End Sub

I would prefer to use formulas as @user10931127 suggested but if you still want VBA code then try this one line VBA code

Sub Sample()
    [C9] = [INDEX(SUM(VALUE(LEFT(A1:A6,1))),)]
End Sub

If you want an explanation how this works then see THIS

Sub sum_first_digit()

Dim Col As Long, X As Long

For Col = 2 To 7
    X = X + Left(Cells(5, Col), 1)
Next Col

Range("C9") = X

End Sub

Please try this.

Sub sum_first_digit()

    Dim colnum As Integer
    Dim sumfirst As Integer
    Dim CelRef As Variant

    For colnum = 2 To 7
        CelRef = Left(Cells(5, colnum).Value, 1)
        sumfirst = sumfirst + Val(CelRef)
    Next colnum

    Range("C9").Value = sumfirst
End Sub

The problem is in the variable CellRef. First you wrote with 2 letters L and after with just 1 letter L.

Always use the comand Option Explicit at the top of your module. It forces you declare all variables and avoid this kind of error.

Correct code:

Sub sum_first_digit()

    Dim colnum As Integer
    Dim sumfirst As Integer
    Dim cellref As Variant 'Change this type to another more specific (int, long or string)

    sumfirst = 0

    For colnum = 2 To 7 Step 1
        cellref = Cells(5, colnum)
        If IsNumeric(Left(cellref , 1)) Then sumfirst = sumfirst + Left(cellref , 1)
    Next colnum

    Range("C9").Value = sumfirst

End Sub

The error is because the variable ** celref ** is not declared and therefore is empty. Left ("", 1) is also empty. This way you're trying to add a text to a number and that's why the types are incompatible.

Try

sumfirst + (Left(celref, 1)) is String

sumfirst is Integer

integer = string --> error Type Mismatch

Sub sum_first_digit()

    Dim colnum As Integer
    Dim sumfirst As Integer

    sumfirst = 0

    For colnum = 2 To 7 Step 1
        cellref = Int(Cells(5, colnum) / 10)
        sumfirst = sumfirst + cellref
    Next colnum

    Range("C9").Value = sumfirst


End Sub