If you can download and install the Morefunc.xll add-in from:
http://xcell05.free.fr/morefunc/english/index.htm
There is a function, COUNTDIFF, that is significantly faster than the
standard:
=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))
I haven't tested it yet in Excel 2007 on really big ranges, 65,000 rows,
but in my (working) version of Excel (2002) it calculates 65,000 rows
without a blip!
--
Biff
Microsoft Excel MVP
"Jai" wrote in message
...
Hi. I had the same problemas pjr. Tried This formula and it worked for a
small subset of my data. When I try it on the full 70 000 entries my
computer
locks up. Is there a more efficient way of doing this? Cheers, Jai
"T. Valko" wrote:
Try this:
=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))
--
Biff
Microsoft Excel MVP
"pjr" wrote in message
...
Hey,
I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them.
many
of
those corporation names are repeated throughout the column, but not all
of
them.
How can I count the total # of corporation names present in that
column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.