Out of curiosity I ran some tests.

The array formula is slightly faster than SUMPRODUCT(--(ISNUMBER(MATCH. The
difference may not be significant but the array formula is also shorter.

=SUMPRODUCT(COUNTIF is "significantly" slower than either of the other 2.

http://img67.imageshack.us/img67/5091/calctimes3ki4.jpg

Calculation Timer code by Charles Williams:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"T. Valko" wrote...
Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!C1:C100,ID,0))))
...

If recalc speed isn't essential,

=SUMPRODUCT(COUNTIF(ID,Sheet2!C1:C100))

should return the same result. If recalc speed is essential, I believe the
array formula

=COUNT(MATCH(Sheet2!C1:C100,ID,0))

would be faster.