Formule nombre de colonnes.

Function NbCol(Plage As Range) As Long
Dim s, i&, j&, Nb&, Chaine As String
For i = 1 To Plage.Count
    Chaine = Trim(Chaine & Application.Trim(Plage(i)) & ";")
Next i
s = Split(Chaine, ";")
For i = LBound(s) To UBound(s)
    If InStr(1, s(i), " ") > 0 Then
        For j = i + 1 To Plage.Count
            If s(j) <> "" Then Nb = Nb + 1
        Next j
    End If
Next i
NbCol = Nb
End Function

=NbCol(AJ7:BC7)

If s(j) <>"" Then Nb = Nb + 1

If Len(s(j)) > 0 Then Nb = Nb + 1

Function Nombre_diff(Plage As Range) As Long
Dim dico As Object, Chaine As String, i As Long, s
For i = 1 To Plage.Count
    Chaine = Chaine & Plage(i) & " "
Next i
Chaine = Application.WorksheetFunction.Trim(Chaine)
s = Split(Chaine, " ")
Set dico = CreateObject("scripting.dictionary")
For i = LBound(s) To UBound(s)
    dico(s(i)) = dico(s(i))
Next i
Nombre_diff = dico.Count
End Function

=Nombre_diff(AJ7:BC7)