Sub ApplyCountif()
    Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long
    Dim RPosition As Long
    Dim rngData As Range

    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

    Const x As Long = -1
    Const sFormula As String = "=IF(R[" & x & "]C[" & x & "]<>RC[" & x & "],COUNTIF(C[" & x & "],RC[" & x & "]),"""")"

    For i = 1 To LastCol Step 2

        LastRow = Cells(Rows.Count, i).End(xlUp).Row - 1
        RPosition = InStr(1, Cells(1, i), 1)

        Application.ScreenUpdating = False

        With Cells(2, i + 1).Resize(LastRow)
            .Formula = sFormula
            .Value = .Value
            If RPosition <> 0 Then
                Set rngData = Cells(2, i + 1).Resize(LastRow)
                rngData = Evaluate(rngData.Address & "*2")
                .NumberFormat = "0;;;"
            End If
        End With

    Next i

    Application.ScreenUpdating = True

End Sub