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