Hi Bob
couldn't agree more <vbg

--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
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.