From my understanding, when you enter a formula that returns an array in a
single cell, it will return the first element of the array. The formula
SUMPRODUCT expects arrays, and in the first case the INDEX formulae are
giving two arrays if the formula is enter as an array formula, while the
second case it is just getting two scalars from the cells H1 and H2.

Hope this helps,
Miguel.

"kayard" wrote:


Hi,

I'm slightly puzzled.

Can anybody explain me why the following formula works as expected
(returns 320) :

=SUMPRODUCT(INDEX(Sheet2!B2:D6;C6;0);INDEX(sheet3! B2:D6;C6;0))

BUT, if I put in cell H1 (for example)

INDEX(Sheet2!B2:D6;C6;0) and

INDEX(sheet3!B2:D6;C6;0) in H2

and then

SUMPRODUCT( H1;2) this doesn't work as expected ?

NB: I paid attention to enter formula in H1 and H2 as matrix (CTRL +
SHIFT + ENTER) ...

Any guess ?


--
kayard
------------------------------------------------------------------------
kayard's Profile: http://www.excelforum.com/member.php...o&userid=24716
View this thread: http://www.excelforum.com/showthread...hreadid=542448