Reference VBA object within worksheet function "countifs"

Tier is a variable but by enclosing it in quotes you are treating it as literal text and the function tries to match the actual word "tier". You need to concatenate the value onto the criteria string:

Sub tiers()

Dim tier As Integer

For Each mycell In Sheets("New File").Range("c8:c27")
    tier = mycell.Row - 7
    mycell.Value = Application.WorksheetFunction.CountIfs(Sheets("Orig File").Range("a10:a99999"), ">0", _
    Sheets("Orig File").Range("u10:u99999"), "=" & tier)
Next mycell

End Sub