How does this formula work

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Understood, thanks!

JE McGimpsey said:
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.
Click to expand...
 
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)
 
Back
Top