Excel equation explanation

[Guest]

Hello,
I have recently come across a use full equation that I use, however I am
perplexed about why it works. I am hoping someone can give me a clear
explanation, here it is:

{=sum(1/(countif(data,data))}

This is an array formula, used to count the distinct entries in a data set -
I know it works I just don't know why, and I have reduced it to subsets of
the equation but that just confused me more.

Thx, C

 

[Rick Rothstein \(MVP - VB\)]

I Googled for "count unique excel" (without the quotes) and this was the
first link returned to me...

http://www.pcmag.com/article2/0,1759,1537551,00.asp

Rick

 

[Peo Sjoblom]

First of all, as written that formula is error prone if there can be any
blank cells in the range you are testing, this is a better way of using this
technique

=SUMPRODUCT(--(data<>""),1/COUNTIF(data,data&""))


now to the way it works

COUNTIF(data,data&"")

will return an array of numbers based on the values occurrence like for
instance

{1;3;1;3;3;1;1;1;1}

where 3 represent that particular value occurring 3 times and the rest of
the values 1 time

this part

1/COUNTIF(data,data&"")

using the same data will return

{1;0.333333333333333;1;0.333333333333333;0.333333333333333;1;1;1;1}

then if you sum that array it will return 6

1+1/3+1+1/3+1/3+1+1+1


--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

I believe it was former Excel MVP David Hager that came up with this, it was
mentioned in John Walkenbach's spreadsheet page in 1999. I can't believe
Neil Rubenking doesn't give credit to David.


--


Regards,


Peo Sjoblom

 

[Guest]

Thanks for the quick response, explanations, & alternative methods!