In article ,
"T. Valko" wrote:

Hi Dom!

Hi Biff!

Long time no see!

Yep, it's been a while! :-)

I've tested COUNTDIFF on 10s of 1000's of rows and it is by far the fastest.

Yeah, if efficiency is a concern, it seems the route to go. :-)

I haven't tested it yet in Excel 2007 on 100,000's of rows.

If you get around to testing it, can you share the results with us?

The standard SUMPRODUCT((R<"")/COUNTIF(R,R&"")) brings my machine to its
knees when R=65535 rows.

Yeah, same here. And SUM(IF(FREQUENCY(.....))) is pretty well the same.

Too bad MS doesn't have a built-in function to do this.

You would think that MS would have included it with their latest
version. After all, it does seem to be a common task.

I'll take a look at the SUM(IF(FREQUENCY(.....))) version.

It's not only more efficient, but it avoids the bug associated with
SUMPRODUCT, discussed here...

http://groups.google.com/group/micro...heet.functions
/browse_thread/thread/140c19c8fb483fb8/c20ec397f954a6ce?lnk=st&q=sumprodu
ct+group%3A*Excel*+author%3Aharlan&rnum=134#c20ec3 97f954a6ce

And it's somewhat more robust against special characters, discussed
here...

http://www.mrexcel.com/forum/showpos...3&postcount=20