counting unique items
 

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?

--
tj

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.
--
tj


"tjtjjtjt" wrote:

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?

--
tj

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(D1:D5)

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

"tjtjjtjt" wrote in message
...
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.
--
tj


"tjtjjtjt" wrote:

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?

--
tj

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

"Biff" wrote in message
...
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(D1:D5)

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

"tjtjjtjt" wrote in message
...
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.
--
tj


"tjtjjtjt" wrote:

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?

--
tj