Sumproduct versus sum with indirect

[dicken]

Hi,

I have asked this question on MS website ;

Redirecting

If I have a an array from indirect ;

  INDIRECT({"Sheet1","Sheet2","Sheet3"}&"!"&"A1",TRUE) ,

which returns a spill of "#VALUES" but if F9'd will show ; = {2,3,4} , which is correct,

my question is why can I wrap this in SUM but not SUMPRODUCT which just returns a zero? ,

if I were to write = SUMPRODUCT( {2,3,4} ) , the correct answer would be returned, yet that {2,3,4} is exactly what the indirect is returning?

Richard.

 

[RoryA]

SUMPRODUCT doesn't work with 3D references, unlike SUM.

 

[dicken]

SUMPRODUCT doesn't work with 3D references, unlike SUM.

Right, I knew I couldn't use just "Sheet1:Sheet3!', syntax but if I used the indirect within it.

 

[RoryA]

You're still getting a 3D reference, which is why it won't spill.