Count different numbers only

Array-enter...

=SUM(IF(LEN(A1:A3),1/COUNTIF(A1:A3,A1:A3)))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

That works great would you mind breaking it down so I can understand how it works though.

Thanks

On 2003-01-27 06:32, Stingrad wrote:
That works great would you mind breaking it down so I can understand how it works though.

Thanks

Click to expand...

Neill,

In B1 enter & copy down:

=IF(LEN(A1),1/COUNTIF($A$1:$A$3,A1),"")

then

=SUM(B1:B3)

The array-formula does the above computations in one go.

If an item's frequency of occurrence is 2, it's contribution to the sum is halved, that is, 1/2 = .5.

Thus, the formula is based on assigning to each token of a type the weight that consists of 1 divided by the number of tokens of the type.

Aladin