I don't think this thread is worth continuing.

I think Pierre is evangelising on something he doesn't properly understand.
A couple of his responses today have demonstrated this, he suggested a -- SP
formula was wrong and should be using * (and he tested the same condition
twice for good measure), and said that Domenic's double lookup formula
didn't work whereas his SP one did. As it happened, his SP one did work, but
so did Domenic's, and as Domenic pointed out, Pierre's would not work if the
result to be returned were text.

I suggest that a generic answer to everyone is to ignore this post, and
understand that there are appropriate times to use SUMIF/COUNTIF,
appropriate times to use array formulae, appropriate times to use VLOOKUP,
and appropriate times to use SUMPRODUCT. There is no universal theory of
everything in Excel formulae :-).

Bob


"JE McGimpsey" wrote in message
...
In article ,
Pierre Leclerc wrote:

When you discover the SUMPRODUCT formula, you can forget about COUNTIF
and SUMIF.

Hard to imagine forgetting

=COUNTIF(A:A,"*a*")

in order to use something like

=SUMPRODUCT(--ISNUMBER(SEARCH("a",$A$1:$A$65535)))

COUNTIF is *FAR* faster, can operate on entire columns/rows, and has the
advantage of being easily interpreted.

How would you replace

=COUNTIF(A:J,"Pierre")

using SUMPRODUCT()?

Also when you discover INDEX/MATCH you can replace VLOOKUP, HLOOKUP
and LOOKUP.

I disagree. Aside from the fact that VLOOKUP is somewhat more efficient
than INDEX/MATCH in most cases, VLOOKUP, HLOOKUP and LOOKUP usually have
the advantage of being more readable. That goes a long way toward
reducing errors in spreadsheets.