ExcelBanter - View Single Post

plinius-et

Quote:

Originally Posted by John Black

In article , says...

John Black;1606387 Wrote:
In article ,
says...-

=SUMPRODUCT(1/COUNTIF(A1:A4,A1:A4))-

I don't really get how this works?

John Black

It sum 1 for every single string, 1/2 for every double string, 1/3 for
every triple string, and so on.
So that the sum report the total number of different strings.

Thanks, that's clever. But I found many descriptions of COUNTIF on the web but I still can't
figure out what it means to have the condition field be a range of cells?

John Black

That formula reports the matrix formed by every single result obtained using every cell of the range.

(1/COUNTIF(A1:A4,A1))+(1/COUNTIF(A1:A4,A2))+(1/COUNTIF(A1:A4,A3))+(1/COUNTIF(A1:A4,A4))

E.