With Excel, I had always found that there was a crual leak with functions : no count distinct !
A simple user function could help to fill the gap.
Here is the code :
Function NbValUniques(laPlage As Range)
Dim ValeursUniques As New Collection
On Error Resume Next
For Each cell In laPlage
ValeursUniques.Add cell.Value, CStr(cell.Value)
On Error GoTo 0
NbValUniques = ValeursUniques.Count
You just have to call the function like any other function from a cell and define the range that you would like to count :