Another way of looking at it might be:

Sub Test()    
    Dim tmpWS As Worksheet
    Dim tmpWS2 As Worksheet
    Dim CriteriaRng_ As Range
    Dim Criteria_ As String
    Dim SumRng_ As Range
    
    ' worksheet code name
    Set tmpWS = Sheet56
    Set tmpWS2 = Sheet57
    
    ' worksheet front end name
'    Set tmpWS = Sheets("Sheet name here")
'    Set tmpWS2 = Sheets("Sheet name here")
    
    With tmpWS
        Set CriteriaRng_ = .Range(.Cells(3, 2), .Cells(173, 2))
        Set Criteria_ = tmpWS2.Cells(3, 9).Value
        Set SumRng_ = .Range(.Cells(3, 5), .Cells(173, 5))
    End With
    
    ActiveCell = Application.SumIf(CriteriaRng_, Criteria_, SumRng)
    
End Sub
Hope this helps