Here goes with my attempt.
Let's start by defining the range A1:A10 to talk specifics.
Bob,John,Bob,Bob,John,Jon,Bob,Bill,Bill,Max
The formula here would be
=SUMPRODUCT((A1:A10<>"")/(COUNTIF(A1:A10,A1:A10&"")))
So in this case, A1 holds Bob which is repeated 4 times, so the COUNTIF part
returns 4.
A2 holds John, so COUNTIF returns 3 for A2.
But A3 also holds Bob, which also returns 4.
And so on, with return values of 4,3,3,4,2,1 (I'll leave that to you to work
through).
So the COUNTIF returns an array of {4,3,4,4,3,3,4,2,1}.
(A1:A10<>"") will test for any blanks in the range, and so will return an
array of {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}.
The array results of the COUNTIF are then divided these results to get a
fractional value of each element of the array. This is the part that
effectively does the counting, as the 4 instances of Bob each return an
array element of 4, which when divided into 1, each give 0.25, and these
when added together gives 1. Voila.
So the array returned by (A1:A10<>"")/(COUNTIF(A1:A10,A1:A10&"")) is
{0.25;0.33333;0.25;0.25;0.33333;0.33333;0.25;0.5;0.5;1}
SUMPRODUCT then adds these up to come up with the number of unique entries,
4 in this case, because each separate value in the test range sums to 1.
BTW, to get a better understanding of what goes on in these things, use the
F9 to evaluate the formula. In the formula bar, select the part of the
formula you wish to evaluate, press F9, and you see the results. Hit Esc to
exit.
