Problem using ADDRESS() in SUMPRODUCT()

[rmellison]

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.

[rmellison]

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

"rmellison" wrote:

> 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.
>
>

[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)

"rmellison" <rmellison@discussions.microsoft.com> wrote in message
news:EF9AB033-AED3-4C7C-A536-4607508A7366@microsoft.com...
> I should add that #VALUE error is not caused by different array size or
not
> committing with Ctrl+Shift+Enter.
>
> "rmellison" wrote:
>
> > 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(I
NDEX($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(I
NDEX($A$9:$A$72,$A$6)&"!B1:CW1")<E$1),INDIRECT(INDEX($A$9:$A$72,$A$6)&"!B2:C
W2"))
> >
> > 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.
> >
> >