creating variable named arrays (or cells/tables)

[Travis]

I have the following SUMPRODUCT formula:

=SUMPRODUCT((Code8>=$B13)*(Code8<=$C13)*(NormalizedAmount8<>"x")*Amount8)

Code8, NormalizedAmount8, and Amount8 are named arrays. My workbook
contains similar named arrays with the only distinquisher being the
final number in the name (e.g., Code arrays 1 through 20 incremented by
1).

For ease of replicating the SUMPRODUCT formula when adding new named
arrays and just adjusting it by the increment, is there a way to make
the number portion of the named arrays variable based upon either a
referenced cell or incrementation in the formula?

Any help is much appreciated!

 

[RagDyer]

The use of the Indirect() function will enable you to increment *and/or*
change the name of the range.

To reference a cell, say A1, that contains the number to use as the final
character in the range name:

=SUMPRODUCT((INDIRECT("Code"&A1)>=$B13)*(INDIRECT("Code"&A1)<=$C13)*(INDIREC
T("NormalizedAmount"&A1)<>"x")*INDIRECT("Amount"&A1))

To have the formula increment the range name as it's copied down a column,
try this, where the formula is starting at *1*:

=SUMPRODUCT((INDIRECT("Code"&ROWS($1:1))>=$B13)*(INDIRECT("Code"&ROWS($1:1))
<=$C13)*(INDIRECT("NormalizedAmount"&ROWS($1:1))<>"x")*INDIRECT("Amount"&ROW
S($1:1)))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Travis]

Thank you RD. That is just what I was looking for!