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
|