Hi KL,

It wasn't a typo on your part, it was in the original question.

The most important point in my comment is that of using range names.

And like I said COUNTIF is the approach of choice.

On average array entered formulas are about 1.1 - 1.2 times slower than
SUMPRODUCT although the files size may be greater for the latter. Most
people are more interested in speed, not file size.

--
Cheers,
Shane Devenshire


"KL" wrote:

"ShaneDevenshire" wrote

I like COUNTIF but don't include the ' in front of the sheet name.

I guess that's an obvious missprint deserving no comments :-)

=SUMPRODUCT(N(LEFT(D)="a"))

The subtle difference is that the SUMPRODUCT formula is about 12 times slower than the COUNTIF one in 5000 cells ;-)

=SUM(N(LEFT(D)="a")) entered as an array

This one is even slower than SUMPRODUCT, although the gap is far less significant

Also, although it is in vogue to
use -- to convert TRUE,FALSE to 1,0 N is shorter in this case.

It is shorter to write, but seems to be slightly slower to calculate (according to C.Williams' plug-in)

Regards,
KL