To Fran Kabel

  • Thread starter Thread starter LEB
  • Start date Start date
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.
 
...
...
Are you aware of any result from SUMPRODUCT that can only be gained using
the -- style rather than the * style?
Click to expand...

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.
 
From someone too lazy to try it himself:

when does:

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

become cost effective?
 
Back
Top