How does this formula work

[Guest]

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

 

[JE McGimpsey]

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.

 

[Guest]

Understood, thanks!

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.

 

[Bob Phillips]

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)