software

Hi,

I'm slightly puzzled.

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

=SUMPRODUCT(INDEX(Sheet2!B26;C6;0);INDEX(sheet3! B26;C6;0))

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

INDEX(Sheet2!B26;C6;0) and

INDEX(sheet3!B26;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: www.excelforum.com/member.php...oamp;userid=24716
View this thread: www.excelforum.com/showthread...hreadid=542448From 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.

quot;kayardquot; wrote:

gt;
gt; Hi,
gt;
gt; I'm slightly puzzled.
gt;
gt; Can anybody explain me why the following formula works as expected
gt; (returns 320) :
gt;
gt; =SUMPRODUCT(INDEX(Sheet2!B26;C6;0);INDEX(sheet3! B26;C6;0))
gt;
gt; BUT, if I put in cell H1 (for example)
gt;
gt; INDEX(Sheet2!B26;C6;0) and
gt;
gt; INDEX(sheet3!B26;C6;0) in H2
gt;
gt; and then
gt;
gt; SUMPRODUCT( H1;2) this doesn't work as expected ?
gt;
gt; NB: I paid attention to enter formula in H1 and H2 as matrix (CTRL
gt; SHIFT ENTER) ...
gt;
gt; Any guess ?
gt;
gt;
gt; --
gt; kayard
gt; ------------------------------------------------------------------------
gt; kayard's Profile: www.excelforum.com/member.php...oamp;userid=24716
gt; View this thread: www.excelforum.com/showthread...hreadid=542448
gt;
gt;


Yes but I entered the formulas in h1 and h2 as array formulas so there
shouldn't be any difference..--
kayard
------------------------------------------------------------------------
kayard's Profile: www.excelforum.com/member.php...oamp;userid=24716
View this thread: www.excelforum.com/showthread...hreadid=542448The difference is that a cell cannot contain an array, just an element of the
array. If you enter a formula like
={1,2,3}
in a cell, even if you enter it as an array formula, it will show the
element 1, and behave as the element 1 (scalar), not as the whole array.

Miguel.

quot;kayardquot; wrote:

gt;
gt; Yes but I entered the formulas in h1 and h2 as array formulas so there
gt; shouldn't be any difference..
gt;
gt;
gt; --
gt; kayard
gt; ------------------------------------------------------------------------
gt; kayard's Profile: www.excelforum.com/member.php...oamp;userid=24716
gt; View this thread: www.excelforum.com/showthread...hreadid=542448
gt;
gt;