Dear JE McGimpsey,
Your view is exactly what happen to my problem. I have a very important
spreadsheet which contains more than 20,000 data located in number of
spreadsheets. I used SUMPRODUCT((x = x )*(y = y)*(z = z),(A:A)) to sum up
the value. The spreadsheet is terrible slow in calculation.
However, you told me that SUMPRODUCT(--(x=x),--(y=y),--(z=z),(aj:ak)) can be
even quick and a lot of faster than SUMPRODUCT((x = x )*(y = y)*(z = z),(A:A))
. As a result, I took your advice and tried the formular, unfortunally
failed! I found your suggested formula: SUMPRODUCT(--(x=x),--(y=y),--(z=z),
(aj:ak)) is just change the operator " * " to "--", that is? How come cannot
work?
Please advice!
Many thanks for your time and effort!
Wilchong
JE McGimpsey wrote:
This ordinary Excel formula: SUMPRODUCT((x = x )*(y = y)*(z = z),(A:A)) can
be replaced by array formula: {SUM(x = x)*( y = y)*(z = z)*(A:A))}.
Both formula will produce the same result. My question is that what is the
extra advantage on "array formula" over the ordinary formula.
Both are actually array formulae, but you don't need to use
CTRL-SHIFT-ENTER with SUMPRODUCT.
In general, presumably because of its optimized multiplication routines,
=SUMPRODUCT((x=x)*(y=y)*(z=z),(aj:ak))
calculates very slightly faster than
{=SUM((x=x)*(y=y)*(z=z)*(aj:ak))}
at least in XL versions prior to XL07 (I haven't seen that version
tested).
However, in the SUMPRODUCT() formula above, the use of the * operator
will cause the
(x=x)*(y=y)*(z=z)
portion to be calculated before passing the result on to those optimized
multiplication routines. So (again, at least in pre-XL07 versions) this
formula is even more efficient and quick to calculate:
=SUMPRODUCT(--(x=x),--(y=y),--(z=z),(aj:ak))
where the use of negation operators to convert boolean (TRUE/FALSE)
values to numeric 1/0s is very fast.
Of course, if you only have the one formula, you probably won't know
what to do with the microseconds that you save with that last formula.
But if you have thousands of these formulae in a calculation intensive
workbook, the difference may be perceptible.
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1