"kalz" wrote
I'm also interested to know how to construct ur prev. formula:
=SUM(IF(LEN(Sheet1!A2:A100)0,1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)))

juz dont understand how the part works? ~
1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)

ok, here's an attempt to explain
(others could do this much better <g)

Based on the sample data,
A2:A100 in Sheet1 contains:

1
2
1
3
<rest are blanks

For the sample data, the part: LEN(Sheet1!A2:A100)0
will resolve to: {TRUE;TRUE;TRUE;TRUE;FALSE;...FALSE}

The IF(LEN(Sheet1!A2:A100)0 will "pick" only the parts in the return from:
1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)
corresponding to TRUE,
i.e. it'll pick the first 4 values in the return from the 1/COUNTIF(...)
(**bear this in mind)

COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)
compares each cell in A2:A100 in Sheet1
to each of the other cells in the same range and
returns an array containing counts

The return from COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100) would be:

2
1
2
1
<rest are zeros

as there are two 1s, one 2 and one 3
in A2:A100 in Sheet1

So 1/COUNTIF(...) would return

0.5 (=1/2)
1 (=1/1)
0.5 (=1/2)
1 (=1/1)
<rest are #DIV/0! (=1/0)

As only the first 4 values in the return from the 1/COUNTIF(...)
will be picked (see ** above), hence the expression:

SUM(IF(LEN(...)0,1/COUNTIF(...)))

would ultimately resolve to : 0.5 + 1 + 0.5 + 1 = 3
which is the number of uniques in the range
--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---