John,

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

In the example that you show, which should be (at least)
=SUMPRODUCT(1/(COUNTIF(A1:A10,A1:A10))) by the way, the COUNTIF with an
array value is being used to count the number of each of times the value in
each cell is repeated.
So in this case, A1 holds Bob which is repeated 4 times, so that part of the
COUNTIF 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}.
The array results of the COUNTIF are then divided into 1 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, which when added together gives
1. Voila.
So the array returned by 1/(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.

I don't recall the case that you mention, but I do recall a formula where I
used SUMPRODUCT to add the values at the end of a mixed text/number field,
like Mike 10. The formula in question was

=SUMPRODUCT(--(MID(A1:A10,FIND*("~",SUBSTITUTE(A1:A10,"%%%","~", LEN(A1:A10)-
LEN(SUBSTITU*TE(A1:A10," ",""))))+1,99)))

Not that the %%% should be a single space, but I put %%% in as the NG wraps
at that point and can lose it.

What this is doing is simply using a tried and trusted formula,
MID(A1,FIND*("~",SUBSTITUTE(A1,"","~",LEN(A1)-LEN(SUBSTITU*TE(A1,"
",""))))+1,99), but this time on a range not a single cell, to get the last
part of a delimited string, delimited by space in this case. So in Mike 10,
it returns 10, In Text String 18 it returns 18. SUMPRODUCT is then used just
to sum the results.

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.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Toppers" wrote in message
...
Could someone please explain how the following works:

=SUMPRODUCT(1/(COUNTIF(A1:A10,A1:A1))

I understand this calculates the number of unique values in a list so if
A1:A5 contains 1 and A6:A10 contains 2 the answer is 2 (items). For
example,
I don't understand how COUNTIF works in this situation i.e where the
comparator is an array.

In a recent question, Bob Phillips used SUMPRODUCT to convert an alpha
string e.g. abcde to a numbers (12345) and summed them. Any explanation
of
how this works would also be appreciated.

I have looked at the XLD site article on SUMPRODUCT and understand the
basics but these more advanced uses are baffling me!

Many thanks in advance.

John