So Biff, what time fragments do those numbers represent?
Are we talking minutes, seconds, milliseconds ... ?
What would those results equate to the OP's scenario?
I have personally saved almost 50% in opening and recalc times (25 down to
14) for large WBs being used as databases simply by changing double lookups
and Vlookups (w/error traps) to Index and Match formulas, so this is *not* a
confrontational question.
Are there really *appreciable* savings between the 2 versions?
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
calculation times screencap:
http://img267.imageshack.us/img267/6...ltimes1rq2.jpg
The multiplication version is slightly faster when the number of variables
is small. As the number of variables increases the calc time also
increases
"signifcantly".
The --(ISNUMBER(MATCH(...)) version is faster in 2 out of 3 tests. Also
note
that the number of variables seems to not slow this version down (although
the number of variables is small).
Calc times measured using Charles Williams RangeTimer method.
--
Biff
Microsoft Excel MVP
"Bernard Liengme" wrote in message
...
Thanks for this, We life and learn
cheers
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
"RagDyer" wrote in message
...
If you've seen my posts over the years, you might have noticed my
*distaste* for the unary form of Sumproduct.
Besides the fact that it *can't* be used on different sized ranges
(column vs. row), and the fact that it allows text numbers to be
by-passed without any notice, it *also* doesn't work in this scenario.
Just revise your test formula from unary to asterisk, which is the form
my suggested formula used.
=SUMPRODUCT((A1:A9={"A","B"})*B1:B9)
No problem with this ... is there?
--
Regards,
RD
-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may benefit
!
-------------------------------------------------------------------------
--
"Bernard Liengme" wrote in message
...
That will earn a #VALUE! error
Let A1:A9 have letters a, b or c in each cell
Let B1:B9 have numbers
Then your method becomes =SUMPRODUCT(--(A1:A9={"A","B"}),B1:B9)
But the part (A1:A9={"A","B"}) generates an 18 element array of TRUE
and
FALSE values. But since B1:B9 is a nine element array, we have a
mismatch.
T Valko's method with ISNUMBER(MATCH overcomes the problem and let one
use {"A","B"}
best wishes from Canada
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
"RagDyer" wrote in message
...
Little shorter:
=SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"})
*'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70)
--
HTH,
RD
-----------------------------------------------------------------------
----
Please keep all correspondence within the NewsGroup, so all may
benefit
!
-----------------------------------------------------------------------
----
"torooo" wrote in message
...
Thanks for your assistance, the formula works (without the extra
parenthesis).
Best