counting unique items

[tjtjjtjt]

I've seen formulas like this posted as a solution for counting unique items
in a column:
=SUMPRODUCT((A1:A20000<>"")/COUNTIF(A1:A20000,A1:A20000&""))

What purpose does the &"" at the end serve?

 

[tjtjjtjt]

It seems to stop blanks in the range from causing the formula to return an
error message, but I'm not sure I see why.

 

[Biff]

Hi!

That's exactly what it does.

If a cell is blank, then:

A1:A20000<>"" will return FALSE in that cells position in the array. Then:

COUNTIF(A1:A20000,A1:A20000) will return 0 for that empty cell. Then:

FALSE/0 will return #DIV/0!

So, the empty text string is used so that:

A1:A20000<>"" will return TRUE, then:

TRUE/0 will return 0.

To see how this formula works, try this in a small test range:

Put some random entries in A1:A5.

In B1 enter this formula and copy down to B5:

=A1<>""

In C1 enter this formula and copy down to C5:

=COUNTIF(A$1:A$5,A1&"")

In D1 enter this formula and copy down to D5:

=B1/C1

And finally, enter this formula in E1:

=SUM(D15)

Try experimenting by putting dupes in A1:A5. Then try removing the empty
string from the Countif formulas and delete some of the entries in A1:A5.

See what happens!

Biff

 

[Biff]

Ooops!

I goofed in my explanation:

So, the empty text string is used so that:

A1:A20000<>"" will return TRUE, then:

TRUE/0 will return 0.

Should be:

So, the empty text string is used so that when:

A1:A20000<>"" returns FALSE

The Countif will count the empty string so that:

FALSE/1 will return 0

Biff