That is why I transposed it.

--
HTH

Bob Phillips

"Biff" wrote in message
...
That returns #VALUE! due to the array of range references being passed
to
Sumproduct.

Can you say that in a different way as I don't know what you mean.

Assume:

H1 = Sheet1
H2 = Sheet2

INDIRECT("'"&H1:H2&"'!A1")

Passes this array to Sumproduct:

{Sheet1!A1,Sheet2!A1}

For some reason Sumproduct won't accept arrays of range references.

N() (or T() for text values) converts those arrays to either numeric or
text
arrays which Sumproduct can then handle.

Not here it doesn't!

Hmmm..... ???

Biff

"Bob Phillips" wrote in message
...
Hi Biff,

"Biff" wrote in message
...
Hi Bob!

That returns #VALUE! due to the array of range references being passed
to
Sumproduct.

=SUMPRODUCT(TRANSPOSE(N(INDIRECT("'"&H1:H2&"'!A1") ))*(N(INDIRECT("'"&H1:H2&"
'!A1"))<100))

Can you say that in a different way as I don't know what you mean.

Also, if:

Sheet1A1 = 100
Sheet2A1 = 10

Formula returns: 110

Not here it doesn't!