Problem using ADDRESS() in SUMPRODUCT()

[Guest]

This is a re-post of a problem from last week, hopefully someone can help...

Why does this formula return #VALUE:
=SUMPRODUCT(--(INDIRECT(INDEX($A$9:$A$72,$A$6)&"!B1:CW1")>D$1),--(INDIRECT(INDEX($A$9:$A$72,$A$6)&"!B1:CW1")<E$1),INDIRECT(ADDRESS(ROW(),COLUMN(B2),4,,(INDEX($A$9:$A$72,$A$6)))&":"&(ADDRESS(ROW(),COLUMN(CW2),4))))

When this formula works as expected:
=SUMPRODUCT(--(INDIRECT(INDEX($A$9:$A$72,$A$6)&"!B1:CW1")>D$1),--(INDIRECT(INDEX($A$9:$A$72,$A$6)&"!B1:CW1")<E$1),INDIRECT(INDEX($A$9:$A$72,$A$6)&"!B2:CW2"))

I cannot use the latter formula because I need to drag it down for each of
several thousand rows, and I would need to change "B2:CW2" manually for every
row.

 

[Guest]

I should add that #VALUE error is not caused by different array size or not
committing with Ctrl+Shift+Enter.

 

[Bob Phillips]

It is because the new code returns the range in an array, and it doesn't
like it.

Try this instead

=SUMPRODUCT(--(INDIRECT(INDEX($A$9:$A$72,$A$6)&"!B1:M1")>D$1),
--(INDIRECT(INDEX($A$9:$A$72,$A$6)&"!B1:M1")<E$1),
INDIRECT(INDEX($A$9:$A$72,$A$6)&"!B"&ROWS($A$1:A2)&":M"&ROWS($A$1:A2)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)