On Oct 3, 8:28*am, "JoeU2004" wrote:
"T. Valko" wrote:
Just to throw some more gasoline on this fire....
--((A1:A10="x")+(A1:A10="y")0)
Is far more intuitive

Thanks. *I believe I said that several times in this thread. *We're just
talking to ourselves ;-).

----- original message -----

"T. Valko" wrote in message

... Just to throw some more gasoline on this fire....

--((A1:A10="x")+(A1:A10="y")0)

Is far more intuitive than:

SIGN((A1:A10="x")+(A1:A10="y"))

Now, to spoil everyone's argument...

ISNUMBER(MATCH is probably the best way to go when doing "OR" on a single
one dimensional array in a formula with multiple conditions.

--
Biff
Microsoft Excel MVP

"JoeU2004" wrote in message
...
"Bernd P" wrote:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))
[....]
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

[...] same nesting level

No it isn't. *I said "function nesting level of 7". *(I meant to write
"limit" instead of "level". *But either one works in this context.)

Excel 2003 has a limit of 7 nested functions (8, counting the outermost).
I don't know if Excel has limit on expression nesting (other than the
formula character limit), but if it does, it's certainly more than 7. *(I
tried only 12, with no error.)

SIGN serves as a nice and decent reminder that
we apply OR criteria

The plus sign does that, not SIGN. *I found SIGN confusing in this
context; that is, until I understood what you were trying to accomplish
with it. IMHO, "0" communicates that more straight-forwardly.

in short: simply better.

That is a matter of opinion. *Reasonable people can disagree.

I already knew your unwavering opinion from elsewhere in this thread. *I
was writing my opinion for Bob and others, not that Bob really needs to
hear it.

On 10,000 rows your version needs 7ms, mine 6ms on
my dual core proc. *Not that it matters here, but...

I agree that the small total difference would not make any difference to
me.

But I'm impressed by your numbers.

I get a difference of about 165.132 msec for "0" and 153.731 msec for
"SIGN". *"SIGN" is about 7% faster. *(But again, 11.4 msec per 10,000 is
not enough for me adopt a different style.)

I have a single-core 2.13GHz CPU running at 2.08GHz. *"My Computer"
Properties now shows a memory speed of 2.13GHz. *I coulda sworn it showed
about 768MHz (or 786?) earlier. *The latter is what I remember vaguely
from the specs when I bought my laptop 4 years ago.

----- original message -----

"Bernd P" wrote in message
....
Of course, that should be:

=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))

Hello,

OT:
Now compare that to:
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

Less function points, same nesting level, and SIGN serves as a nice
and decent reminder that we apply OR criteria; in short: simply
better.

On 10,000 rows your version needs 7ms, mine 6ms on my dual core proc.
Not that it matters here, but...

Regards,
Bernd
told me method how to used mid formula in excel sheet