How does this formula work
 

To count unique records, =sum(1/countif(a2:a100,a2:100)) entered as an array
formula works great except when range contains blanks. Please explain how
this works, I don't understand it

Countif(A2:A100,A2:A100) returns an array of 100 counts of the values in
A2:A100, with unique values returning 1 and replicated values returning
the count of replicates the number of times replicated. E.g.,
{1,2,3,4,2,5} would return {1,2,1,1,2,1}.

1/x inverts the array (e.g., {1,0.5,1,1,0.5,1}) so that each replicate
is only counted it's proportional number of times, and the array is then
summed.




In article ,
"KB" wrote:

To count unique records, =sum(1/countif(a2:a100,a2:100)) entered as an array
formula works great except when range contains blanks. Please explain how
this works, I don't understand it

Understood, thanks!

"JE McGimpsey" wrote:

Countif(A2:A100,A2:A100) returns an array of 100 counts of the values in
A2:A100, with unique values returning 1 and replicated values returning
the count of replicates the number of times replicated. E.g.,
{1,2,3,4,2,5} would return {1,2,1,1,2,1}.

1/x inverts the array (e.g., {1,0.5,1,1,0.5,1}) so that each replicate
is only counted it's proportional number of times, and the array is then
summed.




In article ,
"KB" wrote:

To count unique records, =sum(1/countif(a2:a100,a2:100)) entered as an array
formula works great except when range contains blanks. Please explain how
this works, I don't understand it

To take care of the blanks, use

=SUM((A2:A100<"")/COUNTIF(A2:A100,A2:A100&""))

--

HTH

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


"KB" wrote in message
...
Understood, thanks!

"JE McGimpsey" wrote:

Countif(A2:A100,A2:A100) returns an array of 100 counts of the values in
A2:A100, with unique values returning 1 and replicated values returning
the count of replicates the number of times replicated. E.g.,
{1,2,3,4,2,5} would return {1,2,1,1,2,1}.

1/x inverts the array (e.g., {1,0.5,1,1,0.5,1}) so that each replicate
is only counted it's proportional number of times, and the array is then
summed.




In article ,
"KB" wrote:

To count unique records, =sum(1/countif(a2:a100,a2:100)) entered as
an array
formula works great except when range contains blanks. Please explain
how
this works, I don't understand it