Hello all,

I stumbled across the above formula in a couple of old ng threads. I get
that the formula counts all unique items in a range. I have no idea how it
does so. SUMPRODUCT is fast becoming one of my favorites. This one is
currently over my head. Can any one please detail what exactly it does in
this case? Thanks in advance.

Sorry, I didn't realize it would get cut off in some viewers...

SUMPRODUCT((A2:A210<"")/COUNTIF(A2:A210,(A2:A210&"")))

"Rik Smith" wrote:

Hello all,

I stumbled across the above formula in a couple of old ng threads. I get
that the formula counts all unique items in a range. I have no idea how it
does so. SUMPRODUCT is fast becoming one of my favorites. This one is
currently over my head. Can any one please detail what exactly it does in
this case? Thanks in advance.

if there were 3 XXs in column A it would make each XX be worth 1/3 in the sum
therefore the sum of three 1/3 is 1 for a all the XXs. while if there was
only one YY it would get a 1 value in the sum thus for a list of 3 XXs and 1
YY the sumproduct would =2 and indicate 2 unique values.

"Rik Smith" wrote:

Sorry, I didn't realize it would get cut off in some viewers...

SUMPRODUCT((A2:A210<"")/COUNTIF(A2:A210,(A2:A210&"")))

"Rik Smith" wrote:

Hello all,

I stumbled across the above formula in a couple of old ng threads. I get
that the formula counts all unique items in a range. I have no idea how it
does so. SUMPRODUCT is fast becoming one of my favorites. This one is
currently over my head. Can any one please detail what exactly it does in
this case? Thanks in advance.

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.




--
HTH

Bob Phillips

"Rik Smith" wrote in message
...
Sorry, I didn't realize it would get cut off in some viewers...

SUMPRODUCT((A2:A210<"")/COUNTIF(A2:A210,(A2:A210&"")))

"Rik Smith" wrote:

Hello all,

I stumbled across the above formula in a couple of old ng threads. I
get
that the formula counts all unique items in a range. I have no idea
how it
does so. SUMPRODUCT is fast becoming one of my favorites. This one is
currently over my head. Can any one please detail what exactly it does
in
this case? Thanks in advance.

Thanks Bob! Excellent explanation. I wish you had written some of the built
in help! Also, cool F9 trick! I had no idea it was there.

"Bob Phillips" wrote:

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.




--
HTH

Bob Phillips

"Rik Smith" wrote in message
...
Sorry, I didn't realize it would get cut off in some viewers...

SUMPRODUCT((A2:A210<"")/COUNTIF(A2:A210,(A2:A210&"")))

"Rik Smith" wrote:

Hello all,

I stumbled across the above formula in a couple of old ng threads. I
get
that the formula counts all unique items in a range. I have no idea
how it
does so. SUMPRODUCT is fast becoming one of my favorites. This one is
currently over my head. Can any one please detail what exactly it does
in
this case? Thanks in advance.

<<<"I wish you had written some of the built in help!"

But he did write his own "Help" on many issues.
Check out:

http://www.xldynamic.com/source/xld.html

You'll also find a *detailed* explanation of Sumproduct there at:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Rik Smith" wrote in message
...
Thanks Bob! Excellent explanation. I wish you had written some of the
built
in help! Also, cool F9 trick! I had no idea it was there.

"Bob Phillips" wrote:

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.




--
HTH

Bob Phillips

"Rik Smith" wrote in message
...
Sorry, I didn't realize it would get cut off in some viewers...

SUMPRODUCT((A2:A210<"")/COUNTIF(A2:A210,(A2:A210&"")))

"Rik Smith" wrote:

Hello all,

I stumbled across the above formula in a couple of old ng threads.
I
get
that the formula counts all unique items in a range. I have no
idea
how it
does so. SUMPRODUCT is fast becoming one of my favorites. This one
is
currently over my head. Can any one please detail what exactly it
does
in
this case? Thanks in advance.