To Fran Kabel

[Norman Harker]

Hi Bernie!

Re: Are you aware of any result from SUMPRODUCT that can only be
gained using the -- style rather than the * style?

Only where we use the "," approach to SUMPRODUCT or if we
(inappropriately) have a single condition such as:

=SUMPRODUCT(($A$1:$A$4="ProductA"))

JE has tested the "," approach as being slower. Most often, I don't
worry about speed as with fast processors, it isn't noticeable. But I
think it worth noting JE's point and giving the alternative if large
ranges are involved.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Harlan Grove]

...
...

Are you aware of any result from SUMPRODUCT that can only be gained using
the -- style rather than the * style?

Yes. If the range to be summed could contain nonnumeric text or booleans that
should not be summed, i.e., using SUMPRODUCT as SUMIF(A,C,B) rather than as
COUNTIF(A,C), then

=SUMPRODUCT(--(CriteriaExpression1),--(CriteriaExpression2),ToBeSummed)

gives the same result as

=SUMPRODUCT((CriteriaExpression1)*(CriteriaExpression2),ToBeSummed)

but

=SUMPRODUCT((CriteriaExpression1)*(CriteriaExpression2)*ToBeSummed)

would return #VALUE!. This is so because SUMPRODUCT happily dicards/ignores
corrsponding entries from all its arguments if any of those entries is text or
boolean. This only matters for summing, not counting.

 

[Dave Peterson]

From someone too lazy to try it himself:

when does:

=abs(sumproduct(-(),-(),-(),-(),-()))

become cost effective?