SUMPRODUCT and "--"

[T. Valko]

2. SUMPRODUCT is offered here (and then used or applied) far too often.

Maybe the reason it's applied so often is because it's a very powerful and
useful counting/summing/"lookup" function.

--
Biff
Microsoft Excel MVP

From your website:

My thesis is that SUMPRODUCT has become the biggest time-waster for Excel
©
users who managed to understand its functionality.

If that's what you think then why do you also suggest (on your website)
using SUMPRODUCT instead of SUMIF/COUNTIF?

Wouldn't that be a waste of time? <g>

Hello Biff,

No.

1. For some problems you should use SUMPRODUCT, for some you should
use my UDF's (or other special-purpose UDF's) and to some others you
should apply pivot tables.

2. SUMPRODUCT is offered here (and then used or applied) far too
often.

These two thesises are not forming a contradiction.

Regards,
Bernd

 

[Bernd P]

Maybe the reason it's applied so often is because it's a very powerful and
useful counting/summing/"lookup" function.

Sorry, no again. That's the reason why it got overstated <g>

Regards,
Bernd

 

[T. Valko]

Sorry, no again.

Of course you're entitled to your own opinion.

 

[JoeU2004]

Your ()>0 is only doing half of the job
[....]
you will need to wrap it into paranthesis and to
double unary it (or similar). So: welcome back to
the same number of keystrokes

Yeah, that was an important criterion ;-) ;-).

and nesting levels.

I was referring to the Excel 2003 function nesting level.


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

To each his own.

But philosophically, I think ">0" is the better choice. It clearly
indicates that we expect only values greater than zero to be valid.

When I saw SIGN, my first reaction was: "Why is the guy using SIGN? The
particular argument can never evaluate to -1. So what does he expect?"

Then I thought: "Okay, it works. But why use a function when an operator
will do just fine?" Then I remembered that IF() is treated like an
operator, not a function, so I thought perhaps that is true for all
internal
functions. So there probably is no performance issue. Nonetheless, the use
of SIGN __looks__ inefficient there. And of course, it counts toward the
Excel 2003 nesting limit of 7.

But hey, y'can lead a horse to water, but y'can't make him drink.

Hello,

Your ()>0 is only doing half of the job because its not resulting in
numbers, just boolean values. In order to use it generally within
nested or combined conditions (example: an AND of two OR conditions)
you will need to wrap it into paranthesis and to double unary it (or
similar). So: welcome back to the same number of keystrokes and
nesting levels.

Regards,
Bernd

 

[Bob Phillips]

I think you are being far too purist, far too dogmatic, when you probably
have no idea of what the OP knows/wants/has to achieve, just what he has
posted in their plea for help.

In most questions asked here and on the forums, the OP needs a solution, SP
often provides that spectacularly well. Of course, if it is used, extended
over many cells, the performance will suffer. In those circumstances, the OP
would (should) come back and seek further help. I think the fact that we
seen very few such requests attests to the validity of the solutions
offered.

SP can be abused, as can UDFs. You are whistling in the wind Bernd.

Bob

From your website:

My thesis is that SUMPRODUCT has become the biggest time-waster for Excel
©
users who managed to understand its functionality.

If that's what you think then why do you also suggest (on your website)
using SUMPRODUCT instead of SUMIF/COUNTIF?

Wouldn't that be a waste of time? <g>

Hello Biff,

No.

1. For some problems you should use SUMPRODUCT, for some you should
use my UDF's (or other special-purpose UDF's) and to some others you
should apply pivot tables.

2. SUMPRODUCT is offered here (and then used or applied) far too
often.

These two thesises are not forming a contradiction.

Regards,
Be

 

[Bernd P]

I think you are being far too purist, far too dogmatic, when you probably
have no idea of what the OP knows/wants/has to achieve, just what he has
posted in their plea for help.

In most questions asked here and on the forums, the OP needs a solution, SP
often provides that spectacularly well. Of course, if it is used, extended
over many cells, the performance will suffer. In those circumstances, the OP
would (should) come back and seek further help. I think the fact that we
seen very few such requests attests to the validity of the solutions
offered.

SP can be abused, as can UDFs. You are whistling in the wind Bernd.

Bob

Hello Bob,

OT:

You are entitled to your own opinion. Which is different from mine, as
it seems.

My thesis about SUMPRODUCT might be proven or rejected with
statistical evidence over time.

Fact is that your current SUMPRODUCT example for an OR is showing XOR
criteria and it would fail for overlapping OR ones (for which you do
not offer a solution).

Regards,
Bernd

 

[JoeU2004]

Fact is that your current SUMPRODUCT example for an
OR is showing XOR criteria and it would fail for
overlapping OR ones (for which you do not offer a solution).

I presume you are talking about the example:

=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault"))

The correct statement is: it works when the conditions are
__mutually-exclusive__. You are correct that it does not work otherwise.

XOR is an operation, not a criterion.

I am not nitpicking your use misuse of terms. The formula above does
__not__ implement an XOR insofar as it does not return FALSE when both
conditions are met.

(That is what confused me when I read your web page. Now I understand what
you were trying to say.)

Of course, both conditions cannot be met in Bob's example; ergo, it is
__not__ incorrect for the specific case.

But if one of the ranges were B1:B10 instead of A1:A10, then mutual
exclusion is an important issue.

It would be prudent for Bob to state that in his example, or provide a
second example that demonstrates how non-mutually exclusive conditions
should be handled. As I stated, I think the preferred method would be:

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

although "<>0" is a reasonable alternative.

The unneeded use of another function, like SIGN as you do, is deprecated
because it will not work in some cases in Excel 2003, namely when the user
reaches the function nesting level of 7. Then, the hapless user would need
to find an alternative form, namely the above. So you might as well do it
the way that works more generally.

(Of course, it would probably be better to pick a different example
altogether. I'm just piggybacking Bob's example to demonstrate form.)


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

 

[JoeU2004]

Nitpick errata....

The formula above does __not__ implement an XOR insofar
as it does not return FALSE when both conditions are met.

I should say the "__equivalent__ of FALSE", namely zero, since the
expression returns numeric values, not boolean (true/false) values.


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

 

[JoeU2004]

Errata....

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

Of course, that should be:

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



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

 

[Bernd P]

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

 

[JoeU2004]

=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 -----

 

[Bob Phillips]

I had moved on to the point (that you raised) that ... SUMPRODUCT is offered
here (and then used or applied) far too often... and you drag it back to a
topic that I thought we had agreed to disagree on days ago. Sigh!


Bob

 

[T. Valko]

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

=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 -----

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

 

[T. Valko]

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

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

Eh, those might be bad examples but I'm sure you all get my drift.

--((A1:A10="x")+(B1:B10="y")>0)
SIGN((A1:A10="x")+(B1:B10="y"))

--
Biff
Microsoft Excel MVP

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

=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 -----

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

 

[JoeU2004]

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 -----

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

=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 -----

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

 

[T. Valko]

I believe I said that several times in this thread.

Yes, I'm backing your position! Although, I have on occasion used SIGN. It
just depends on what I think of at the moment.

--
Biff
Microsoft Excel MVP

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 -----

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

=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 -----

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

 

[Bernd P]

ISNUMBER(MATCH is probably the best way to go when doing "OR" on a single

one dimensional array in a formula with multiple conditions.

Hello Biff,

OT:
You mean --ISNUMBER(MATCH())? Very interesting.

But it takes quite a long array to make it quicker than SIGN() on
average, I guess.

Regards,
Bernd

 

[Bob Phillips]

I agree with you Biff, ISNUMBER makes far more sense, SIGN makes the least
(although I too have been offering SIGN for many years. I don't recall
anyone using it before Frank Kabel and I came up with it in a NETWORKDAYS
solution - not saying we were first, but we certainly arrived at the
solution independently).

But this thread is really dead now, isn't it best left to wither?

Bob

I believe I said that several times in this thread.

Yes, I'm backing your position! Although, I have on occasion used SIGN. It
just depends on what I think of at the moment.

--
Biff
Microsoft Excel MVP

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 -----

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


=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 -----

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

 

[rock star]

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 -----


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

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

Is far more intuitive than:

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.

=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 -----
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