Problem using ADDRESS() in SUMPRODUCT()
 

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),I NDIRECT(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),I NDIRECT(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.

Problem using ADDRESS() in SUMPRODUCT()
 

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),I NDIRECT(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),I NDIRECT(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.

Problem using ADDRESS() in SUMPRODUCT()
 

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" wrote in message
...
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(ADD RESS(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(IND EX($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.