How to make array of noncontig cells for SUMPRODUCT?
 

I think I am making this more difficult than necessary ....

I want to do SUMPRODUCT where at least one array must
be composed of a "range" of non-contiguous cells -- namely,
one cell from each of N worksheets.

Ostensibly, I want
SUMPRODUCT(A1:Z1,ARRAY(Sheet1!A1,...,Sheet26!A1))

There is an ARRAY() function in VBA. But I do not find
any excel built-in function with the same capability. Did
I overlook it?

I created my own VBA function, myarray(). I also created
mytranspose() to handle the case when the first
SUMPRODUCT range is a column (A1:A26), not a row. It
obviates the need to enter SUMPRODUCT() as array formula.

These VBA functions are trivial to write. I cannot believe
"Bill's kids" cannot figure out how to do them as built-ins.
So I believe I "must be" overlooking the obvious.

Please help me. What simpler alternatives exist?

How to make array of noncontig cells for SUMPRODUCT?
 

Hi!

Not so easy using worksheet functions!

=SUMPRODUCT((A1:Z1)*(TRANSPOSE(N(INDIRECT("sheet"& ROW(INDIRECT("1:26"))&"!A1")))))

Has to be array entered because of Transpose.

Biff

" wrote
in message ...
I think I am making this more difficult than necessary ....

I want to do SUMPRODUCT where at least one array must
be composed of a "range" of non-contiguous cells -- namely,
one cell from each of N worksheets.

Ostensibly, I want
SUMPRODUCT(A1:Z1,ARRAY(Sheet1!A1,...,Sheet26!A1))

There is an ARRAY() function in VBA. But I do not find
any excel built-in function with the same capability. Did
I overlook it?

I created my own VBA function, myarray(). I also created
mytranspose() to handle the case when the first
SUMPRODUCT range is a column (A1:A26), not a row. It
obviates the need to enter SUMPRODUCT() as array formula.

These VBA functions are trivial to write. I cannot believe
"Bill's kids" cannot figure out how to do them as built-ins.
So I believe I "must be" overlooking the obvious.

Please help me. What simpler alternatives exist?